Get next day of week

Get next day of week

Generic formula 

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

Explanation

If you want to find the next specific day of week (i.e. the next Wednesday, or Friday, or Monday) given a specific start date, you can use a generic formula that uses the WEEKDAY function with something called "day of week" (dow). With this formula, you can do things like:

How the formula works

Formulas that use the concept of "day of week" are based on the idea that each day of of the week has a specific number. By default, Excel assigns 1 to Sunday and 7 to Saturday. So, the WEEKDAY function, when given a date, will return a number between 1 and 7. In a nutshell, this formula first rolls the date forward by 7 days, then rolls back to the correct date by subtracting the result of a calculation that uses the WEEKDAY function.

In the example formula, B6 is the date 1/16/2015, and the formula in C6 is:

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

The number 2 represents the day of week (dow) that you want, in this case, Monday.

To solve this formula, Excel first adds 7 to the starting date in C6, then reduces B6+7-2 to 42025 (the serial number in Excel's date system that represents 1/21/2015). Next, Excel calculates the day of week for that date (4) and subtracts that from the the original start date + 7 (42027 = 1/23/2015) to get a final date value of 42013 = 1/9/2015

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

If you want 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)

Note: If the date supplied is the same day of week you're want to retrieve, the same date will be returned.

Get next day of week

Generic formula 

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

Explanation

If you want to find the next specific day of week (i.e. the next Wednesday, or Friday, or Monday) given a specific start date, you can use a generic formula that uses the WEEKDAY function with something called "day of week" (dow). With this formula, you can do things like:

How the formula works

Formulas that use the concept of "day of week" are based on the idea that each day of of the week has a specific number. By default, Excel assigns 1 to Sunday and 7 to Saturday. So, the WEEKDAY function, when given a date, will return a number between 1 and 7. In a nutshell, this formula first rolls the date forward by 7 days, then rolls back to the correct date by subtracting the result of a calculation that uses the WEEKDAY function.

In the example formula, B6 is the date 1/16/2015, and the formula in C6 is:

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

The number 2 represents the day of week (dow) that you want, in this case, Monday.

To solve this formula, Excel first adds 7 to the starting date in C6, then reduces B6+7-2 to 42025 (the serial number in Excel's date system that represents 1/21/2015). Next, Excel calculates the day of week for that date (4) and subtracts that from the the original start date + 7 (42027 = 1/23/2015) to get a final date value of 42013 = 1/9/2015

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

If you want 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)

Note: If the date supplied is the same day of week you're want to retrieve, the same date will be returned.