Starting Date for a Week Number

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.