Excel Office

Excel How Tos, Tutorials, Tips & Tricks, Shortcuts

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

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)

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.

Also See:   Steps to create Dynamic calendar grid 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.

Also See:   Conditional formatting gantt chart weekends in Excel

Leave a Reply

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