# How to calculate next day of week in Excel

To return the next specific day of week (i.e. the next Wednesday, or Friday, or Monday) with a given start date, you can use a formula based on the WEEKDAY function.

## Formula

=date+7-WEEKDAY(date+7-dow)

## Explanation

In the example shown, the formula in D6 is:

=B6+7-WEEKDAY(B6+7-2)

Where B6 contains the date Friday, January 16, 2015, and 2 represents Monday, the “dow”. The result is Monday, January 9, 2015.

To get a different day of week, use a different value for “dow” in the formula. With default settings, the WEEKDAY function uses a scheme where Sunday =1, Monday=2, Tuesday=3, Wednesday=4, Thursday=5, Friday=6, and Saturday=7.

## How the formula works

In Excel, the concept of “weekday” or “day of week” is based on the idea that each day of of the week has a specific number. With default settings, the WEEKDAY function treats Sunday as the first day of the week (1), and Saturday as the last day (7). When given a valid date, the WEEKDAY function will return a number between 1 and 7.

To find the next day of week, this formula first rolls the date forward by 7 days, then steps back to the correct date by subtracting the result of a calculation that uses the WEEKDAY function. In the example shown the formulas in row 6 are as follows:

=B6+7-WEEKDAY(B6+7-7) // C6, get next Saturday =B6+7-WEEKDAY(B6+7-2) // D6, get next Monday

In the first formula (C6), the number 7 (at the end) represents Saturday. In the second formula (D6), the number is 2, for Monday.

In D6, the formula is solved like this:

=B6+7-WEEKDAY(B6+7-2) =42027-WEEKDAY(42025) =42027-4 =42023 =1/9/2015

Note: when the given date is already the desired day of week, the original date will be returned.

### Next day of week from today

To get the next day of week from the current date, you can use the TODAY function, like so:

=TODAY()+7-WEEKDAY(TODAY()+7-dow)