Beginning of a Future Week

For David's purposes, weeks always begin on Monday. If he enters a date into cell A1 (which may not be a Monday), he needs to calculate the date of the Monday of the week that is 35 days (5 weeks) in the future from cell A1. The date that is returned by the calculation must always be the Monday that starts that future week. He wonders how to put together such a formula.

You can perform this type of calculation by relying on the WEEKDAY function. Here, for instance, is a simple formula that, at first blush, seems to work:

=A1+37-WEEKDAY(A1)

The WEEKDAY function, by default, returns a value of 1 through 7, where Sunday is 1, Monday is 2, Tuesday is 3, and so on. So, adding 37 days to the date in A1 and then subtracting the weekday value would seem to give the correct result. It doesn't, however, in the case where A1 contains a Sunday. In that case, the formula evaluates as follows:

=A1+37-1

In other words, if A1 is a Sunday, then the calculated date is 36 days in the future. While that is still a Monday, it is a Monday a week beyond what David wants. His weeks begin on Monday and end on Sunday, so a Sunday date in A1 should return the Monday of the week that is 5 weeks from the Monday previous to that Sunday.

This could get hairy in a hurry, and people have solved the hairiness by adding in CHOOSE functions and relying on lookup tables. There is an easier way, however, and that is to utilize the second by often ignored parameter available for WEEKDAY. In Microsoft's Excel documentation, this parameter is referred to as "return type." It allows you to specify how you want what WEEKDAY returns to be modified. You can find full information about it here:

https://support.microsoft.com/en-us/office/60e44483-2ed1-439f-8bd0-e404c190949a

If you check that documentation, you can easily figure out that you could use the following variation of WEEKDAY in the formula:

=A1+35-WEEKDAY(A1,3)

This returns, from WEEKDAY, a value of 0 through 7 where Monday is 0, Tuesday is 1, Wednesday is 2, and so on. In other words, the week goes from Monday (0) through Sunday (6), which is exactly what David needs for his calculation.

If you go back to the documentation page at the Microsoft site, you'll note this one interesting phrase in the "Return Type" table: "Behaves like previous versions of Microsoft Excel." In other words, like versions before the second parameter was added to the WEEEKDAY function. But, search as you might, the documentation page never explains what constitutes a previous version.

Only through further digging and testing does one discover that the second parameter was added in Excel 2010. So, if your workbook is used by people still relying on Excel 2007, then this variation of WEEKDAY won't work. In that case, you may be best to rely on a rather unique formula:

=A1+35-MOD(A1-2,7)

The first part of this formula is identical to the first part of the previous WEEKDAY formula—it adds 35 days to the date in A1. Then, the MOD function is used to return a value of 0 to 7 for the day of the week in cell A1, which is exactly what WEEKDAY(A1,3) does.

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