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

Removing Extra Paragraph Marks

Tired of having too many paragraph breaks in your document? You can get rid of the extra paragraph marks by using the ...

Discover More

Easily Changing Chart Data Ranges

Want a handy way to make the data ranges for your chart more dynamic? Here are some great ideas you can put to work right ...

Discover More

Saving a Preview with Your Template

Templates provide a collection of styles and boilerplate for new documents. Selecting the right template by filename only ...

Discover More

Create Custom Apps with VBA! Discover how to extend the capabilities of Office 365 applications with VBA programming. Written in clear terms and understandable language, the book includes systematic tutorials and contains both intermediate and advanced content for experienced VB developers. Designed to be comprehensive, the book addresses not just one Office application, but the entire Office suite. Check out Mastering VBA for Microsoft Office 365 today!

More ExcelTips (ribbon)

Determining Business Quarters from Dates

Many businesses organize information according to calendar quarters, especially when it comes to fiscal information. ...

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

Incrementing Months in Dates

Excel can easily store dates. If you want to increment a date by one month, there are a number of ways you can accomplish ...

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 six more than 9?

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.