Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • Convert text string to valid reference in Excel using Indirect function
  • Count rows that contain specific values in Excel
  • Multi-criteria lookup and transpose in Excel
  • Left Lookup in Excel
  • How to get last row in text data in Excel

Data Analysis Examples

  • How to count table columns in Excel
  • What-If Analysis: Scenarios and Goal Seek in Excel
  • Working With Tables in Excel
  • Number and Text Filters Examples in Excel
  • Understanding Pivot Tables in Excel

Data Validation Examples

  • Excel Data validation allow uppercase only
  • Excel Data validation must contain specific text
  • Excel Data validation number multiple 100
  • Prevent invalid data entering in specific cells
  • Excel Data validation require unique number

How to calculate most recent day of week in Excel

by

If you want to find the most recent Wednesday, or Saturday, or Monday, given a specific start date, you can use a formula that uses the MOD function.

Formula

=date-MOD(date-dow,7)

Note: If the the date has the same day of week, date will be returned.

Worked Example:   Even and Odd function in Excel

Explanation

 

Formulas that use the concept of “day of week” are based on the idea that each day of of the week has a specific number. By default, Excel assigns 1 to Sunday and 7 to Saturday.

In the example formula, B6 is the date 1/16/2015, and the formula in C6 is:

=B6-MOD(B6-7,7)

The number 7 (the number argument in the MOD function) represents the day of week (dow) that you want.

Worked Example:   Add decimal minutes to time in Excel

To solve this formula, Excel first subtracts the dow (7 in this case) from the date, then feeds the result into the MOD function as the number. MOD returns the remainder of dividing that number by 7, which is then subtracted from date.

=B6-MOD(B6-7,7)
=B6-MOD(42013,7)
=B6-6
=42014
=1/10/2015

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

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

 

Worked Example:   Highlight multiples of specific value in Excel

Post navigation

Previous Post:

How to calculate months between dates in Excel

Next Post:

How to calculate next scheduled event in Excel

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others
  • How to add a line break with a formula while joining text strings in Excel
  • How to Separate Text Strings in Excel
  • How to strip protocol and trailing slash from URL in Excel
  • Remove file extension from filename in Excel
  • FIXED function: Description, Usage, Syntax, Examples and Explanation
  • Convert time to time zone in Excel
  • Convert text date dd/mm/yy to mm/dd/yy in Excel
  • Calculate series of dates by workdays in Excel
  • Basic Overtime Calculation Formula in Excel
  • Get last day of month in Excel
  • ACCRINT function: Description, Usage, Syntax, Examples and Explanation
  • Tax rate calculation with fixed base in Excel
  • DDB function: Description, Usage, Syntax, Examples and Explanation
  • DISC function: Description, Usage, Syntax, Examples and Explanation
  • NPV function: Description, Usage, Syntax, Examples and Explanation
Acronyms, Abbreviations, Initialism & What They Stand For
© 2022 xlsoffice . All Right Reserved. | Teal Smiles