Summary

To get the next specified weekday (i.e. the next Monday, the next Tuesday, the next Wednesday, etc.) starting from a given start date, you can use a formula based on the MATCH function and the TEXT function. In the example shown, the formula in E5 is:

=B5+MATCH(C5,TEXT(B5+{0,1,2,3,4,5,6},"ddd"),0)-1

Where B5 contains a start date, and C5 contains the target weekday, given as the 3-letter abbreviation "Mon". The result is Monday, October 2, 2023 — the first Monday after Friday, September 29, 2023. 

Generic formula

=A1+MATCH("Mon",TEXT(A1+{0,1,2,3,4,5,6},"ddd"),0)-1

Explanation 

In this example, the goal is to get the next specified weekday, starting on a given start date. So for example, with a valid start date in column B, we want to be able to ask for the next Monday, the next Tuesday, the next Wednesday, and so on. This article describes two distinct ways of solving this problem. The first way, as seen in the worksheet above, is based on the TEXT function and the MATCH function. This method is flexible and intuitive since you can simply ask for the desired day of week by name. The second method is based on the WEEKDAY function. This is the "traditional approach", although I find it harder to understand. Both approaches are explained below, and both work equally well.

MATCH + TEXT option

One way to solve this problem is with the MATCH function and the TEXT function using a generic formula like this:

=A1+MATCH("Mon",TEXT(A1+{0,1,2,3,4,5,6},"ddd"),0)-1

In this formula, we assume the start date is in cell A1, and that our desired target day of week is Monday, abbreviated "Mon". Here's how this formula works, working from the inside out:

  1. A1+{0,1,2,3,4,5,6}: This creates an array of dates representing the current day and the next six days.
  2. TEXT(A1+{0,1,2,3,4,5,6},"ddd"): This converts each date in the array to a text string representing the abbreviated day of the week (e.g., "Sun", "Mon", "Tue", etc.).
  3. MATCH("Mon",TEXT(A1+{0,1,2,3,4,5,6},"ddd"),0): This searches for the position of the specified day ("Mon" for Monday) in the array of abbreviated day names. The position becomes the number of days to add to the original date in A1 to reach the next specified weekday.
  4. -1: Since the array includes the current day, we subtract 1 to adjust the position to the correct number of days to add.
  5. A1 + : Finally, the calculated number of days is added to the original date in A1 to get the date of the next specified weekday.

The main trick in this formula is: TEXT(A1+{0,1,2,3,4,5,6},"ddd"). This is the way Excel evaluates this part of the formula in E5:

=TEXT(B5+{0,1,2,3,4,5,6},"ddd")
=TEXT(45198+{0,1,2,3,4,5,6},"ddd")
=TEXT({45198,45199,45200,45201,45202,45203,45204},"ddd")
={"Fri","Sat","Sun","Mon","Tue","Wed","Thu"}

What we see above is that the date in cell B5 (September 29, 2023) is the serial number 45198 in Excel's date system. When we add the array constant {0,1,2,3,4,5,6} to this number, we get an array that represents Sep. 29 plus the next 6 days. The text string "ddd" is a custom number format for a 3-letter abbreviation for weekday names. TEXT runs on all seven dates and returns their abbreviated names in an array like this:

{"Fri","Sat","Sun","Mon","Tue","Wed","Thu"}

When we drop this array back into the original formula, we get the following:

=B5+MATCH(C5,{"Fri","Sat","Sun","Mon","Tue","Wed","Thu"},0)-1
=B5+MATCH("Mon",{"Fri","Sat","Sun","Mon","Tue","Wed","Thu"},0)-1
=B5+4-1
=45198+4-1
=45201

With "Mon" in cell C5, MATCH returns 4, and the final result is 45201, which is Monday, October 2, 2023.

This formula is intuitive and flexible since you can easily change the specified day by replacing "Mon" with the abbreviation of any other day of the week. It will return the original date in A1 if it is already the specified weekday, and the next occurrence of the specified weekday otherwise. There is no need to know the numeric values for each day, as with the WEEKDAY formula below.

WEEKDAY option

Another more traditional way to solve this problem is with the WEEKDAY function. The WEEKDAY function takes a date and returns a number between 1-7 representing the day of the week. By default, WEEKDAY uses a scheme where Sunday =1, Monday=2, Tuesday=3, Wednesday=4, Thursday=5, Friday=6, and Saturday=7. So, for example, WEEKDAY returns 6 for September 29, 2023, which is a Friday:

=WEEKDAY("29-Sep-2023") // returns 6

We can use this behavior to create a formula that returns the next given weekday with a generic formula like this:

=A1+7-WEEKDAY(A1+7-n)

Where A1 contains the start date, and n represents the day number of the weekday you want. So for example, in the first formula below n is 7 to get the next Saturday while in the second formula n is 2 to get the next Monday:

=A1+7-WEEKDAY(A1+7-7) // next Saturday
=A1+7-WEEKDAY(A1+7-2) // next Monday

You can see this approach used in the workdsheet below, where start dates appear in column B and the target weekday is specified with a day number in column C:

WEEKDAY option for getting the next specified day of week

Notes: (1) The table in G5:H11 is a key for day numbers, provided for reference only. (2) When the given date is already the desired day of week, the original date will be returned.

This formula is a more difficult to understand than the first formula. The gist is that it 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. Working from the inside out, here are the steps

  1. A1 + 7: Moves forward 7 days to the same weekday of the next week.
  2. A1 + 7 - n: Shifts back by n days to a "reference day". The result depends on the weekday provided as n.
  3. WEEKDAY(A1 + 7 - n): Calculates the weekday number of the reference day.
  4. A1 + 7 - WEEKDAY(A1 + 7 - n): Subtracts the weekday number of the reference day from the date calculated in step 1.

The key is the reference day calculated by A1 + 7 - n. This reference day is always in the same week as the original date A1. By calculating the WEEKDAY of the reference day and subtracting it from A1 + 7, we are essentially finding the next occurrence of the desired weekday in the current week. For example, assume A1 contains the date Sep 29, 2023 (which is a Friday), and n given as 2 (for Monday):

  1. A1 + 7: Sep 29, 2023 + 7 days = Oct 6, 2023 (a Friday).
  2. A1 + 7 - n: Oct 6, 2023 - 2 (since n is 2 for Monday) = Oct 4, 2023 (a Wednesday).
  3. A1 + 7 - n: WEEKDAY(Oct 4, 2023) = 4 (Wednesday 4 in the WEEKDAY's default scheme).
  4. A1 + 7 - WEEKDAY(A1 + 7 - n): Oct 6, 2023 - 4  = Oct 2, 2023 (a Monday).

The final result is Oct 2, 2023, the next Monday after the given date.

Next day of week from today

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

=TODAY()+7-WEEKDAY(TODAY()+7-n)
Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.