Roll back weekday to Friday base on a particular date in Excel
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.
In the example shown, the formula in C5 is
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.
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:
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.