Bottom line: Learn a few tips and techniques for calculating week numbers from dates, the number of weeks between dates, and how to display the week number in cells for reporting.
Skill level: Beginner
As I mentioned in this week's newsletter, our baby boy was born last week. Everything went well with the delivery and we are so happy to have our new little 8-pound bundle of joy in our lives.
During my wife's pregnancy, everything was measured in weeks. There are 40 weeks in a full term pregnancy, we seemed to have a doctor's appointment every other week, and we were definitely counting down the weeks until his estimated arrival date.
Of course, I kept track of most of these schedules and calculations in Excel. So, I wanted to share some of the formulas and calculations I used to calculate week numbers. These tips will also be useful for financial models, data analysis, and summary reports. Babies aren't the only things measured in weeks… 🙂
Download the Example File
Download the example Excel file to follow along with the article.
#1 – WEEKNUM Function: Calculate the Week Number of the Year
We can use the WEEKNUM function to quickly determine how many weeks have elapsed from January 1st to a given date in the same year. The following formula returns 11 because 11 weeks have elapsed since January 1, 2017.
=WEEKNUM("3/15/2017")
Result: 11
We can also reference a cell that contains a date. If the date 3/15/2017 was in cell A2, then the following formula would return the same result.
=WEEKNUM("A2")
Result: 11
The WEEKNUM function has an optional argument that allows us to specify the day of the week to start on. By default, WEEKNUM starts the week on Sunday. We can change that by adding any of the following numbers to the second argument (return_type). The image below shows the numbers and days for the return_type argument.
It's important to note that the function only calculates the number of weeks from the specified date in the same year. If we used “3/15/2016” in the WEEKNUM function, the result would be the number of weeks from January 1st, 2016, which is 12. The week starts on Sunday, so the same date might have a different week number result from year to year.
One drawback of the WEEKNUM function is that we are stuck with January 1st (system 1) or the week containing the first Thursday of the year (system 2). It would be great if there was a 3rd argument that allowed us to specify the day of the year to start on. This would be helpful for fiscal year calendars. Unfortunately, we don't have that feature yet.
Check out my article on how the calendar date system works in Excel for more details on how dates are stored as numbers.
#2 – Calculate the Number of Weeks Between Dates
To determine how many weeks elapsed between two dates, we can use a simple formula to find the number of days between the dates, then divide by 7.
=(B2-A2)/7
The formula will return a decimal number. We can change the number format to a Fraction to display the number of days.
Or, we can use one of the ROUND functions to ROUND UP or ROUND DOWN to the nearest whole number.
This formula can also be used to countdown the number of weeks until your next birthday, vacation, holiday, quarter-end, year-end, etc. We can use the TODAY() function to return today's date in a cell. The date will be updated with the current date every time we open or calculate the workbook.
#3 – Display the word “Week” in the Number Format
We might want to display the word “Week” before the week number in the cell. There are a few ways to do this in Excel. My preferred method is to use a custom number format to add the word “Week” before the week number.
The custom number format is:
"Week" #
The advantage of the custom number format is that the number remains a number, instead of text. This makes it easier to sort by week number in filtered ranges and pivot tables.
If the number is stored as text, it will not sort properly unless we use two digits for the week number.
Therefore, I recommend using the custom number format to keep the data type of the cells as numbers. This also helps keep the sort order when we create slicers on the Week Number field with the custom number format.
I also have an article on 3 ways to display the weekday name of a date.
Other Methods for Calculating Week Numbers?
I hope you enjoyed those tips for working with week numbers in Excel. These can be very useful for summary reports and week-over-week analysis.
Here are some related articles on working with dates in Excel.
- How Dates Work in Excel: The Calendar System Explained + Video
- Filter a Pivot Table or Slicer for the Most Recent Date or Period
- Grouping Dates in a Pivot Table VERSUS Grouping Dates in the Source Data
- How to Create Month to Date (MTD) Comparisons with a Pivot Table + Video
- 3 Ways to Get the Day Name for a Date
What did I miss? Do you have any additional tips for working with weeks? If so, please leave a comment below with any suggestions or questions.
Well, I'm off to change a diaper and take a nap… Thank you! 🙂
Hello,
I have been approached to do some continuing education workshops for health care providers (physicians, nurses, etc) using software packages including excel. I don’t have a clue where to start. I know programs but that is it, Would you be interested in a partnership of some sort. I would want to have something the way you present in a webinar format.
There are lots of details such as giving certificate of completion and hours. I don’t know how to advertise or much of anything. I have presented these live and now would like to do this on-line.
Let me know if you are interested. This would be profit sharing project
Don Johnson
=WEEKNUM(“3/15/2017”) -11 to get Week 1
in particular #3 – Display the word “Week” in the Number Format with custom number format is helpful.
Thank you, this helpful. I work in education and we often work in units of weeks.
Congatulations with the birth of your son.
In Europe we use a different week definition so these examples are not ok for me. We use an ISO standard for weeknumbering. Our weeks start on monday not sunday and not on janaury 1st. Everyone on mainland Europe should be aware of that. Use the ISOWEEKNUM function instead!
and congrats on the newborn!
Great tips! I especially found the formula for calculating week numbers using ISO standards very useful. It’s so handy for organizing my project timelines. Thanks for sharing!
Great tips on working with week numbers in Excel! The custom number format for displaying “Week” before the number is especially helpful for keeping the data organized.
Great tips! I’ve always struggled with calculating week numbers in Excel, but your clear explanations and examples make it so much easier to understand. Thanks for sharing!
Great tips! I’ve always struggled with calculating week numbers in Excel, but your clear explanations and step-by-step guidance made it so much easier to understand. Thank you for sharing these practical methods!
Great tips! I often struggle with week numbers in Excel, so your explanations are super helpful. The formula breakdowns make it easy to follow. Thanks for sharing!