Get most recent day of week

This Excel tutorial shows how to get the most recent day of the week with a formula that uses MOD function. Once provided with a specific start date, the most recent day of the week will be returned.

Get the most recent day of the week

The Excel formula to get the most recent day of the week, that is, the most recent Wednesday, or Saturday, or Monday, uses the MOD function. The MOD function returns the remainder of two numbers once divided. For example, MOD (5, 2) = 1.

Furthermore, Excel allocates the day of the week to different numbers. The number 1 to Sunday and the number 7 to Saturday.

Formula

=date-MOD(date-dow, 7)

The following arguments are required to get the most recent day of the week:

  • Date: This argument is the specific start date which is inserted into the cells.
  • MOD: This is the MOD function.
  • dow: This argument represents the day of the week as a number that you want (Monday or Wed or Fri)
  • 7: This is the divisor which is constant.

Explanation

Follow these steps to get the most recent day of the week using Excel formula as illustrated above

1.Open Excel.

2. Define your table header and the specific start date in the cells of the first column.

3. Select the cell B2 by clicking on it.

Figure 1. How to get the most recent day of week in Excel 1

4. Insert the Excel formula into the cell or the Formula Bar

Figure 2. How to get the most recent day of the week in Excel 2

5. Press Enter to assign the formula to B2

6. All the cells in column B and C show the date of the most recent day of the week.

Figure 3. How to get the most recent day of the week from any date

Example 1

If you want to get the most recent day of the week from any specific date, using the above illustration, the general formula becomes:

= A2 - MOD (A2 - 4, 7)

Excel subtracts the dow (4 in the above example) from the date. It continues by sending the result into the MOD function as the number. The MOD function returns the remainder of dividing that number by 7, which is eventually deducted from the date.

= A2 – MOD (A2 – 4, 7)

= A2 – MOD (40235, 7)

=A2 – 6

= 40233

=Wednesday, February 24, 2010

After getting the result, the targeted cells need to be formatted as per the desired format to show weekday with dates as shown above.

Example 2

If you want to get the most recent day of the week from the present date, the general formula is used with the Today function. Therefore, the formula used in this case will be:

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

Figure 4. Example of how to get most recent day of week from the present day in Excel 1

Figure 5. Example of how to get the most recent day of the week from the present day in Excel 2

Note

  • If the given start date becomes identical with the most recent day of the week, the date will be returned.
Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar