Get most recent day of week

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:

The formula solution

The generic version of the formula looks like this:

=date-MOD(date-dow,7)

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

=B5-MOD(B5-7,7)

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:

=B5-MOD(B5-7,7)
=B5-MOD(42020-7,7)
=B5-MOD(42013,7)
=B5-6
=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:

=TODAY()-MOD(TODAY()-dow,7)

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