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

Negatives in Pie Charts

Pie charts are a great way to graphically display some types of data. Displaying negative values is not so great in pie ...

Discover More

Turning Off Screen Updating

When working with macros, you can often speed up processing by turning off the updating of the screen. Best news is that ...

Discover More

Printing A4 on Letter Size Paper

For those around the world using the metric system, the standard size for many documents is referred to as an A4 page. If ...

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)

Changing How Excel Determines which Year to Use

When you enter a date into a cell and you omit the year, Excel helpfully adds the current year to the date. If you want ...

Discover More

Inserting Tomorrow's Date

You can use a couple of different worksheet functions to enter today's date in a cell. What if you want to calculate ...

Discover More

Counting Dates in a Range

Excel makes working with a list of dates relatively easy. If you have a list of dates, you may need to know how many of ...

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 9 - 4?

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.