I need help with creating a formula

Copper Contributor

I have a large amount of data that was generated over a period of 4 months equivalent to 10 weeks, recorded in days and time. How do I formulate my cell to break the data into weeks, then filter the data into 10 weeks?   




4 Replies


To display date in calendar week you can use the formula: =WEEKNUM(A4) in D4.

This article describes the formula syntax and usage of the WEEKNUM function in Microsoft Excel.

WEEKNUM function


After that, you could add up the calendar week with a simple sum.

Here is a small example of the formula: =SUMIF(D4:D35,D4,E:AA)


Hope I was able to help you with this info.



I know I don't know anything (Socrates)


@NikolinoDE thank you, but I think I did not explain properly what I actually need help in. The first screen shot picture that I shared is exactly what you had explained. What I am having a challenge with is how to use xIfn and UNIQUE properly, after getting the number of weeks, how do I filter the data further?

From the screen captures it looks like you're using Excel 2016 which doesn't support UNIQUE and dynamic arrays. Analyzing this data set might be a small chore with formulas in that version. Another approach to make is tabling the data and unpivoting from Column E to the end which will make your data run mostly vertical. From there you could pivot the data. If you're able to upload a mock sample of the data set, myself or someone else could arrange it for you.

@Patrick2788 thank you so much, I am trying the pivot suggestion right now. My computer has Excel 2019. 


This is the section, I am struggling with