In this tutorial, you will learn how to check if the day for a selected date is Monday and set it to Friday. This is enabled by using Excel functions IF and WEEKDAY, which checks: if Monday, roll back to Friday. Above all, the prerequisite for the formula to work is to have all date cell formatted as dates.
If Monday, roll back to Friday
=IF(WEEKDAY(date)=2, date-3, date)
The function WEEKDAY returns a number of a day in a week for a selected date, so a date is the only parameter of this function. The numbers are returned as follows:
1 – Sunday
2 – Monday
3 – Tuesday
4 – Wednesday
5 – Thursday
6 – Friday
7 – Saturday
The IF function checks a logical condition and returns one value if the condition is true, while the other value if the condition is false. The parameters of the function are: Logical condition – in our case check if WEEKDAY(date) is equal to 2 (Monday), Value if true – in our case if the condition is true, we will return the previous Friday date, which is date – 3, Value if false – in contrast, if the condition is not true, we will return the same date. As a result, IF function will work like: If Monday, roll back to Friday.
In the example, we will see how to check if the day for the selected date is Monday and roll back to Friday date. Therefore, let’s see how the formula looks like:
Figure 1. If Monday, roll back to Friday
As you can see in the picture, we first check the weekday for B3 cell (26-Feb-2018). As this is Monday, the function WEEKDAY(B3) will return number 2, because it stands for Monday. Further, in IF function, we return B3-3 if the condition is true and B3 if the condition is false. Because the condition is true, in the cell C3 we will get value B3 – 3, which is 3 days back from Monday 26-Feb-2018 (23-Feb-2018).