Summary

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. In the example shown, the formula in C5 is

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

Generic formula

``=IF(WEEKDAY(date)=2,date-3,date)``

Explanation

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.

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.

Author

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.