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
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
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
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
=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.
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:
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
- 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.