To find the most recent day of the week (i.e. most recent Monday, Tuesday, Wednesday, etc.) with a given starting date, you can use a formula based on the MOD function. In the example shown, the formula in cell D5 is:


With a start date of 16-Jan-2016 in cell B6 and the target day of the week (dow) given as 7 (for Saturday), the result is 10-Jan-2015, a Saturday.

Generic formula



In this example, the goal is to create a formula that will return the most recent day of the week, given a date and a target day of the week, abbreviated as "dow" in the generic formula. Excel tracks the day of the week internally as a specific number for each of the seven days. By default, Excel assigns 1 to Sunday and 7 to Saturday as seen below:

Day of week Number
Sunday 1
Monday 2
Tuesday 3
Wednesday 4
Thursday 5
Friday 6
Saturday 7

The formula solution

The generic version of the formula looks like this:


In the example shown, cell B5 contains the date 1/16/2015, and the formula in D5 is:


The number 7 is the target dow (day of week). Excel first subtracts the dow (7 in this case) from the date, then feeds the result into the MOD function as the number with 7 as the divisor. MOD returns the remainder after division, which is subtracted from the start date. At a high level, the formula works like this:

  • date - dow - Shifts the original date back by dow days to create a reference point in the past.
  • MOD(date - dow, 7) - Normalizes the shift to a value within the current week (0-6 days). This value represents how many days back we are from a complete week cycle.
  • B5-MOD(B5-7,7) - Subtracting the normalized value from the original date gives the most recent target day of the week.

The code below shows how Excel evaluates the formula step-by-step:

=42014 // 10-Jan-2015, a Saturday

The result is 42014, which is January 10, 2015, (a Saturday) in Excel's date system.

Most recent day of the week today

If you want to get the most recent day of the week from the current date, you can use the TODAY function like this:


Note: If the date is already the target day of the week, the date will be returned unchanged.

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.