Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • Add Outline to Data in Excel
  • Understanding Anova in Excel
  • How to count table rows in Excel
  • Error Bars in Excel
  • Excel Line Chart

References

  • How to use Excel FORMULATEXT function
  • How to calculate two-way lookup VLOOKUP in Excel Table
  • Two-column Lookup in Excel
  • Find closest match in Excel
  • Basic INDEX MATCH approximate in Excel

Data Validations

  • Excel Data validation date in specific year
  • Excel Data validation date in next 30 days
  • Excel Data validation allow uppercase only
  • Excel Data validation must not contain
  • Excel Data validation whole percentage only

Roll back weekday to Friday base on a particular date in Excel

by

Case study:

If Monday, roll back to Friday

To check the weekday of a date, and roll back to Friday when the date is a Monday, you can use the IF and WEEKDAY functions.

Formula

=IF(WEEKDAY(date)=2,date-3,date)
If-Monday-roll-back-to-Friday Roll back weekday to Friday base on a particular date in Excel

Explanation

In the example shown, the formula in C5 is

=IF(WEEKDAY(B5)=2,B5-3,B5)

How this formula works

The WEEKDAY function returns a number, 1-7, that corresponds to particular days of the week. By default, WEEKDAY assumes a Sunday-based week, and assigns 1 to Sunday, 2 to Monday, and so on, with 7 assigned to Saturday.

Worked Example:   How to calculate nth day of week in month in Excel

In this case, we only want to take action if the date in question is Monday. To test, we use this expression inside the IF function:

WEEKDAY(B5)=2

If the logical expression returns TRUE, we know the date is a Monday, so we subtract 3 to “roll back” to Friday. If the expression returns FALSE, we simply return the original date.

Worked Example:   Calculate number of hours between two times in Excel

Post navigation

Previous Post:

Get day from date in Excel

Next Post:

Calculate loan interest in given year 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

Logical Functions

  • IFERROR function: Description, Usage, Syntax, Examples and Explanation
  • FALSE function: Description, Usage, Syntax, Examples and Explanation
  • IF with boolean logic in Excel
  • How to use Excel NOT Function
  • AND function: Description, Usage, Syntax, Examples and Explanation

Date Time

  • Roll back weekday to Friday base on a particular date in Excel
  • Convert date to Julian format in Excel
  • Calculate years between dates in Excel
  • Basic timesheet formula with breaks in Excel
  • EOMONTH function: Description, Usage, Syntax, Examples and Explanation

Grouping

  • Group numbers with VLOOKUP in Excel
  • If cell contains one of many things in Excel
  • Group numbers at uneven intervals in Excel
  • Map inputs to arbitrary values in Excel
  • Categorize text with keywords in Excel

General

  • How to calculate percentage of total in Excel
  • List sheet names with formula in Excel
  • Excel Operators
  • How to generate random times at specific intervals in Excel
  • AutoRecover file that was never saved in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning