There is a formula that gives the date of the previous Monday.
This is especially helpful so that in instances where the week is split between two months (e.g. January 31 falls on a Tuesday, February 1 on a Wednesday), you can find the date of last Monday and give all the succeeding weekdays the same week number.
Once you have the same number for all the dates in that week, you can then figure out how to switch that to count from weeks 1 through 5 for any given month.
To start, let’s take January 1, 2017 as an example and adjust accordingly.
First, get the weekday associated with this date.
Cell D6 = WEEKDAY(B6,3)
- Use return_type=3 where Monday is 0 and Sunday is 6.
You will get a 0 for January 1.
Drag this formula down.
On January 3, which is a Tuesday, the count returns a 1.
This implies that you are 1 day away from Monday.
While January 8 (Sunday) returns a 6, since you are 6 days away from Monday.
You will notice that it resets back to 0 for the next Monday, January 9.
These values can be used to correct the days and display the appropriate week number.
Going back to January 2 (Monday), think about what you want to display.
In the first case, you would want to display the day number.
For January 2, you want it to return a 2 for the entire week, while for the week starting January 9, you want it to return a 9 for the inclusive dates.
To achieve this, use Excel’s DAY() function.
The approach will be to get the day associated to the specific date and deduct the weekday shown earlier:
Cell D6 = DAY(B6-WEEKDAY(B6,3))
For January 2, the formula results to a 2-0, and returns a 2.
For January 3, it results to 3-1, and returns a 2, and so on.
Up to this point, you will get the same number for each week.
Proceed to using these results and convert them into week number 1 through 5 for each month.
There are many ways to do this.
One way is to wrap this into the WEEKNUM() formula.
Use the WEEKNUM() formula of a date and use the DAY() argument, but being careful to look at the same month and same year.
For the WEEKNUM() formula, since you will be splitting it up and having DAY() as separate, you should use the DATE() function instead:
Cell D6 = WEEKNUM(DATE(YEAR(B6),1,DAY(B6-WEEKDAY(B6,3))))
In the meantime, fix the month parameter as 1 for the formula to look at January.
Drag this formula down.
You will notice that for weeks that have been split between two months, such as the week containing January 31 (Wednesday) and February 1 (Thursday), the values returned are correct.
To make the outputs look similar, apply a combination of the IF() and WEEKDAY() functions to hide any results for Saturdays and Sundays.
Use the return_type=2 where Monday is 1 and Sunday is 7.
If the weekday of a date a is greater than 5, which means it is a Saturday or Sunday, then it should display nothing.
Otherwise, it should display the week number.
Cell D6 = IF(WEEKDAY(B6,2)>5,””,WEEKNUM(DATE(YEAR(B6),1,DAY(B6-WEEKDAY(B6,3))))