Starting Date for a Week Number

Written by Allen Wyatt (last updated May 31, 2025)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, 2021, 2024, and Excel in Microsoft 365


2

Robbie can use the WEEKNUM function to determine the week number in which a date occurs. He wonders if there is a way to do the reverse, to determine the starting date for a particular week number in a given year.

Before getting into figuring out the starting date for a week number, you need to realize that there are two general ways of calculating week numbers. The traditional way is to use the WEEKNUM function, which is apparently what Robbie is using. It returns a week number using January 1 as the first week of the year. The function restarts week numbers with every Sunday during the year. Thus, if January 1 is on a Saturday, then the next day (Sunday) will be in week number 2.

The other method is to use ISO week numbering, which is done with the ISOWEEKNUM function. In this scheme, week 1 is the first week in any year that contains a Thursday. Further, all weeks begin on Mondays. Because of this, the first few days of a year could belong to the last week in the previous year. Thus, you can only assume that January 4 of any given year will be in week 1 in the ISO world. (January 1, 2, and 3 could be in the previous year's weeks if the year begins on a Friday, Saturday, or Sunday.)

Note that not only do weeks begin on different days, but the starting week is determined differently. This means that you need different formulas to determine the starting date for a week number based on which numbering scheme is used.

Let's assume that cell A1 contains a year, and cell B1 contains a desired week number. If that week number is in the WEEKNUM scheme, as Robbie mentions, then the following formula will return the correct starting date for that week:

=DATE(A1,1,1) - WEEKDAY(DATE(A1,1,1),1) + 1+ (B1 - 1) * 7

Note that the formula bases the week number count on the week in which January 1 occurs but then shifts the starting date based on the day of the week on which January 1 falls. The last part of the formula determines the number of full weeks that occurred before the desired week number. Format the result as a date, and you are set. Remember, as well, that since weeks always start with Sundays, the returned date will always be a Sunday.

If you are working with ISO week numbers, then the following formula should be used to determine the starting date:

=DATE(A1,1,4) - WEEKDAY(DATE(A1,1,4),2) + 1 + (B1 - 1) * 7

The biggest difference with this formula is that the base date is January 4 and the shifting is done based on whether the Monday occurs before January 4 or not. Again, format the result as a date, and you are set. Also, ISO weeks always begin on Mondays, so the date returned will always be a Monday.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (11098) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, 2024, and Excel in Microsoft 365.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Changing the Number of Headings in an Outline

When viewing a document in Outline view, you have complete control over how much outline detail is shown on-screen. This ...

Discover More

Stopping DATE Fields from Updating when Opening a Document

A normal DATE field shows the current date, so it is constantly changing. This can cause problems in a document where you ...

Discover More

Ranges on Multiple Worksheets

Referring to a range of cells on the current worksheet is easy. But what if you want to refer to a range on a number of ...

Discover More

Program Successfully in Excel! This guide will provide you with all the information you need to automate any task in Excel and save time and effort. Learn how to extend Excel's functionality with VBA to create solutions not possible with the standard features. Includes latest information for Excel 2024 and Microsoft 365. Check out Mastering Excel VBA Programming today!

More ExcelTips (ribbon)

Calculating Fractions of Years

When working with dates and the relationship between dates, Excel provides a variety of worksheet functions that may ...

Discover More

Pulling All Fridays

It can be handy to know when specific weekdays occur within a range of dates. Figuring out this information, using ...

Discover More

Converting an Unsupported Date Format

Excel makes it easy to import information created in other programs. Converting the imported data into something you can ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is three less than 3?

2025-06-01 11:18:36

J. Woolley

The Tip says WEEKNUM "restarts week numbers with every Sunday during the year." That's true only if its optional 2nd argument is 1 (the default) or 17.
The Tip says, "Let's assume that cell A1 contains a year, and cell B1 contains a desired week number." If A1 is 2025, B1 is 1, and C1 has the Tip's first formula
    =DATE(A1, 1, 1) - WEEKDAY(DATE(A1, 1, 1), 1) + 1 + (B1 - 1)*7
then the date in C1 is 12/29/2024 and WEEKNUM(C1, 1) is 53, not 1.
This seems inconsistent with Microsoft's description of WEEKNUM: "The week containing January 1 is the first week of the year, and is numbered week 1."
One way to resolve this conundrum is to say that week number 1 always starts on January 1 even if January 1 is not a Sunday. In this case the formula in C1 should be
    =LET(x, DATE(A1, 1, 1), y, (x - WEEKDAY(x, 1) + 1 + (B1 - 1)*7),
        IF(YEAR(y) < A1, x, y))
When A1 is 2025 and B1 is 1, the date in C1 is 1/1/2025 (a Wednesday) and WEEKNUM(C1, 1) is 1.


2025-06-01 09:55:54

J. Woolley

For related discussion, see https://excelribbon.tips.net/T012603_Calculating_the_Last_Day_in_a_Week_Number.html


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.