Help with Conditional Formatting Dates

[deynSTW]

Hi All,

Could someone help with with the condtional formatting of dates and differnet rules please.

If A1 has a date of :

Monday - Friday 07:00 - 21:00 and Saturday - Sunday 07:00 - 16:30 = B1 to be coloured AMBER
Monday - Friday 21:01 - 06:59 and Saturday - Sunday 16:31 - 06:59 = B1 to be coloured GREEN

If B1 has a date of less than today = B1 to be coloured RED

Any help would be much appreciated thanks.

 

[njimack]

the below works for Amber, you can tweak the times for Green:

 =OR(AND(WEEKDAY(A1,2)<=5,TIME(HOUR(A1),MINUTE(A1),0)>=TIME(7,0,0),TIME(HOUR(A1),MINUTE(A1),0)<=TIME(21,0,0)),AND(WEEKDAY(A1,2)>=6,TIME(HOUR(A1),MINUTE(A1),0)>=TIME(7,0,0),TIME(HOUR(A1),MINUTE(A1),0)<=TIME(16,30,0)))