Hello All,
Am looking for an excel formula which will help me group dates into weeks.
For me calendar year starts on 1st November 2015 and end on 31st October 2016.
Week starts on Monday and ends of Sunday.
Looking forward to hearing from you all!!
Regards,
DJ
I think an easy way to go about this is to use a helper table on a separate tab similar to the one i have attached. Then depending on your data you can do a vlookup or index match, to get the acquired week number.
Hi -
Since I don't have any sample data to look at, I assume your dates are in one lone column. So, copy this into a cell adjacent to your first date and then copy down:
=IF(WEEKNUM(D8,2)>=44,WEEKNUM(D8,2)-43,WEEKNUM(D8,2)+9)
This should give you a week number for each date based on weeks starting on a Monday and November 1 being Week 1.
Note that in the case of 1-Nov-2015 was a Sunday, so 2-Nov-2015 is a Monday and is Week 2. that means 26-Oct through 31 Oct. is in Week 1. You will have calendar oddities like that.
Hope this helps.
____________________________________________
If this has solved your problem, please edit the thread title to add the word [SOLVED] at the beginning. You can do this by
-Go to the top of the first post
-Select Thread Tools
-Select Mark thread as Solved
If I have been particularly helpful, please "bump" my reputation by pressing the small star in the lower left corner of my post.
One more thing: My test data I experimented with started in cell D8, so you will have to adjust the cell reference when you paste the formula in. Also, once you have week numbers for several dates, you can use COUNTIF, SUMPRODUCT, Pivot Table, etc. to summarize the information as you need.
what would be the formula if week starts from Sunday and ends on Saturday?
In the WEEKNUM formula, the second element is the start day. So for Monday, you enter 2. For Sunday, you would change the 2's to 1's.
Hello jmor! Your formula works.
Now what changes do I need to bring about in the formula to pick Monday dates instead of week number
Would it help if you can use a PivotTable?
You can group dates into weeks without the need for formulas:
1. Create the Pivottable with dates in the Row Labels section
2. Right click any of the dates and select "Group..."
3. Select "Days" (unselect any other options), then change "Number of days" to 7.
When I test it out with some sample data, it selects the earliest date in the data as the start of the week.
Hi -
The following formula will result in the serial number for the NEXT Monday of whatever date you put in cell D8. So, for example, if you put Sunday 1-Nov-15 in Cell D8, the next Monday is 2-Nov-15. If you put in 2-Nov-15, the formula yields 2-Nov-15. If you put in 3-Nov-15, the next Monday is 9-Nov-15. And so on.
=IF(2-WEEKDAY(D8)>=0,D8+2-WEEKDAY(D8),D8+2-WEEKDAY(D8)+7)
Hope this helps.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks