Go Back

How to Get the Most Recent Day of the Week in Excel

This Excel tutorial shows how to get the most recent day of the week with a formula that uses the 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 using MOD

=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 of formula

Follow these steps to get the most recent day of the week using the 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 the 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 the most recent day of the 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.

Still need some help with Excel formatting or have other questions about Excel? Connect with a live Excel expert here for some 1 on 1 help. Your first session is always free. 

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

I'd like to create a formula that looks up the most recent value for a set of data.
Solved by X. B. in 30 mins
We need a Vlookup to complete the following task: Identify the duplicates Addresses in column H and locate the most recent (date in column T) - if there are any empty cells in that row, take the next most recent data for the same Address and replace the blank with it. The idea is to get a complete set of data for each address.
Solved by C. B. in 28 mins
I need a formula to remove customer duplicates but keep only records of most recent dates of sales
Solved by E. W. in 28 mins

Leave a Comment

avatar