Forum Discussion
I need help with creating a formula
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.
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.
