There are different approaches to creating reports which involve getting the week numbers for each month.
Before jumping in and creating the report, you will have to think about how you’ll handle the cutoff week.
Do you always want to start in week 1 the moment you get to the first of the month?
Do you consider the last days of January and the first days in February to all be in week 5?
Take this data set which has the dates from January 1, 2017, to the beginning of the year 2018 as an example:
Setting up the data
For the sake of demonstration and checking, I have used a Weekday column (A) to show that there is no week count for days that fall on weekends.
To set this up, equate it to the date and merely change the formatting of the cell.
Cell A5 = B5
Press CTRL + 1 to display the Format Cells window.
Alternatively, you can right click on the cell and select Format Cells.
In the Type, you can choose however you want the data to be displayed.
To give you an idea, dates are pure numbers.
January 1, 1900 is equivalent to 1 and as each day passes, it increments by 1.
In this example, we will use “dddd” to display the weekday (Sunday, Monday, etc).
Other format type are yyyy for year and mmmm for the month.
Cases
Whichever case you are going with for, the report will decide the approach to count the week number.
Taking the end of January and the beginning of February as an example, where January 31 is on a Tuesday and February 1 falls on a Wednesday:
- Case 1: February 1 is counted as Week 1 for February
- Case 2: February 1 is still counted as Week 5 of January. Week 1 starts on the 1st
In Case 1, it is possible to have a week with only 1 weekday (e.g. if the 1st day of the month falls on a Friday, or if the last day of the month falls on a Monday).
While Case 2 starts counting the first full week of the month starting on a Monday.
Case 1: Using the WEEKNUM() function to start a new week every 1st of the month
Excel’s WEEKNUM() function is used for Case 1.
Its syntax is:
= WEEKNUM(serial_number, [return_type])- serial_number – this is the date you want to convert
- return_type – how the week starts. You will have an option to pick any way of the week. By default, the week starts on a Sunday.
The formula for cell C5 now becomes:
Cell C5 = WEEKNUM(B5)Drag the formula down to the last row (or double click on the lower right corner of the cell).
You will notice that the values rum from week 1 to 53 and then resets to week 1 when it starts a new year.
However, this does not count the 1st of the month as a new week when it doesn’t fall on a Monday.
In this example, we want February 1 to be counted as week 1 of February instead of week 5 of January.
To understand how to fix this, we will go through it step by step.
Whenever a new month is started, the week counter should reset back to 1.
Subtract the current week number with the week number of the 1st of the month and increment it as it goes.
We will be using a combination of the DATE() function to find the serial number of the 1st of a specific date, and the YEAR() MONTH() functions to find what year and month it should use.
Cell C5 = WEEKNUM(B5)-WEEKNUM(DATE(YEAR(B5),MONTH(B5), 1))The number 1 is used since we want to find the serial number of the 1st day of that month so that we can eventually find its corresponding week number.
You will again notice that the week now starts counting at 0.
Add a 1 to the formula to address this.
Cell C5 = WEEKNUM(B5)-WEEKNUM(DATE(YEAR(B5),MONTH(B5), 1)) + 1Apply the formula down to the last row and now you have the correct week number for February 1 .
Now all that’s left to do is to not display anything when a date falls on a weekend.
Use a combination of the IF() and WEEKDAY() functions here.
The syntax of the WEEKDAY() function is:
= WEEKDAY(serial_number, return_type]- serial_number – the date you are checking, B5
- return_type – tells what number to return for specific days.
By default it uses 1 – Numbers 1 (Sunday) through 7 (Saturday).
In this case, for simplification, use 2 – Numbers 1 (Monday) through 7 (Sunday), which will return the number 6 and 7 for weekends.
We can then use an IF() statement to check if the value returned is bigger than 5 (Friday).
After integrating the IF() statement, the formula now becomes:
Cell C5 = IF(WEEKDAY(B5,2)>5,””, WEEKNUM(B5)-WEEKNUM(DATE(YEAR(B5),MONTH(B5), 1)) + 1)The week number is now blank when the date falls on a weekend.
When you inspect the data, you will notice that when the 1st of the month falls on a weekend, the first weekday of that month starts as week #2 and skips week #1:
This is happening because for the instances where the 1st of the month falls on a Saturday, the formula counts it as part of the 1st week.
However, our cutoff period is a Monday, not a Sunday.
So you should tweak the formula to handle this instance by using another IF() statement.
This will tell the formula not to add 1 if the 1st of the month falls on a Saturday (7).
Cell C5 = IF(WEEKDAY(B5,2)>5, ””, WEEKNUM(B5)-
IF(WEEKDAY(DATE(YEAR(B5),MONTH(B5),1)=7, WEEKNUM(DATE(YEAR(B5),MONTH(B5),1)),
WEEKNUM(DATE(YEAR(B5),MONTH(B5), 1)) + 1))Apply the formula for all the rows and it should now display the correct week numbers.
Case 2: Using the WEEKDAY() function to reset the week number to 1 every time the first Monday of the next month is reached
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))))
Feel free to Download the Workbook HERE.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
