Beginning of a Future Week

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


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, 2021, 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

Numeric Value and Percentage Value in a Graph Column

Need more than a single data label on a data series? Here's a relatively easy way to get what you need.

Discover More

Replacing Links with Values

Need to get rid of the links in your workbook but save the values that were retrieved by those links? It could be easy or ...

Discover More

Error in Linked PivotTable Value

Excel allows you to link to values in other workbooks, even if those values are in PivotTables. However, Excel may ...

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)

Month for the Nth Sunday

Doing math with dates is quite easy in Excel. As this tip illustrates, this fact makes it easy to figure out the Nth ...

Discover More

Displaying a Number as Years and Months

How do you display a number of years, such as 3.67 years, as a number of years and months? It’s simple to do with a ...

Discover More

Assuming Dates are in the Current Century

Type a date into Excel that includes a two-digit year, and you may not always get the year in the century you intended. ...

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 more than 1?

There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)


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.