Conditional Formatting With Dates And Weekend Special Rules

0
votes

I need to highlight cells based on the date in column AG. I am using conditional formatting but can't seem to get the formula right.

Logic:

  • If Today() is a Monday I need to highlight the cell if the date in AG is the PREVIOUS Friday, Saturday and Sunday
  • If Today() is a Tuesday through Friday then I need to highlight if the date in AG is the previous day

I started the formula like this but it is not working properly:

=OR(TODAY()=WEEKDAY(3),WEEKDAY(4),WEEKDAY(5),WEEKDAY(6),AG10=TODAY()-1,OR(IF(TODAY()=WEEKDAY(2),AG10<TODAY()-4)))
1
excel-formula

1 Answers

0
votes

Use

=OR(AND(WEEKDAY(TODAY())>= 3,WEEKDAY(TODAY())<=6,INT(AG10)=INT(TODAY()-1)),AND(WEEKDAY(TODAY())=2,INT(AG10)>=INT(TODAY()-3),INT(AG10)<=INT(TODAY()-1)))