Nov 10 2022 10:57 PM
Hi all,
I have this formula located in Conditional Formatting (as shown below). It highlights the cell on every Fridays. If the Friday is a holiday, it highlights the next workday e.g., Monday. Holidays are in Cmd worksheet. I would like to modify it by highlighting the cell the day before the holiday e.g., Thursday or Wednesday in case of Thu and Fri are consecutive holidays. Thanks in advance, I will greatly appreciate any help in this regard.
=TODAY()=WORKDAY(TODAY()+1-WEEKDAY(TODAY(),15)-1,1,Cmd!$A$6:$A$17)
Nov 11 2022 03:50 AM
SolutionTry this formula:
=IF(ISERROR(MATCH(TODAY(),Cmd!$A$6:$A$17,0)),TODAY()=TODAY()+7-WEEKDAY(TODAY(),16)-ISNUMBER(MATCH(TODAY()+1,Cmd!$A$6:$A$17,0))-ISNUMBER(MATCH(TODAY()+2,Cmd!$A$6:$A$17,0)))
Nov 11 2022 03:50 PM
Nov 11 2022 03:50 AM
SolutionTry this formula:
=IF(ISERROR(MATCH(TODAY(),Cmd!$A$6:$A$17,0)),TODAY()=TODAY()+7-WEEKDAY(TODAY(),16)-ISNUMBER(MATCH(TODAY()+1,Cmd!$A$6:$A$17,0))-ISNUMBER(MATCH(TODAY()+2,Cmd!$A$6:$A$17,0)))