When we enter the date and check the specified weekday for the date and want to roll it back to the required weekday, like check if Monday, roll back to Friday, then we need to use the Excel IF and WEEKDAY functions together in a formula.
Figure 1. If Monday, Roll Back to Friday
Formula Syntax
The generic syntax for the formula is;
=IF(WEEKDAY(date,2)=1, date-3, date)
Where,
- The Excel WEEKDAY function returns a number from 1 to 7 identifying the day of the week for a date according to the return-type index number, like for return_type index number 2, the WEEKDAY returns numbers 1 (Monday) through 7 (Sunday).
- Date – It is the date value entered as cell reference to check the weekday for.
- The IF function checks a logical condition and returns one value if the condition is TRUE, else the other value if the condition is FALSE.
Figure 2. The Formula Syntax to Check If Monday, Roll Back to Friday
Example
In the example, we have a list of date values in column B and will check if the weekday for the date is Monday, then roll it back to Friday date, else return the same date in column C. Using the above formula syntax, we will use the formula in cell C2 and copy it down, such as;
=IF(WEEKDAY(B2,2)=1,B2-3,B2)
The logical condition part of the formula, WEEKDAY(B2,2)=1, checks if the weekday for a date is equal to 1 (Monday), then subtract 3 days from the given date value (B2-3) to roll back to Friday date, else return the same date value.
Figure 3. The Final Output of the Formula
Instant Connection to an Expert through our Excelchat Service:
Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.
Leave a Comment