Conditional Formating Required for changing Dates based on 2 different cell dates

[salsy]

Hi,

I creating a permit register.

End Goal : Have the text in both Cells in "Start Date" and "Finish Date" have the same colour to determine if the Permit is 'Expired', 'Current' or set in the 'future'

Column H (Permit Start Date) and Column I (Permit Finish Date).

Permit dates do not have a set amount of time and are subject to the individual inputting data - they will always be changing.



So this is how I want it to look:

[TABLE="width: 178"]
<colgroup><col width="114" style="width: 86pt; mso-width-source: userset; mso-width-alt: 4169;"> <col width="123" style="width: 92pt; mso-width-source: userset; mso-width-alt: 4498;"> <tbody>[TR]
[TD="width: 114, bgcolor: transparent"]Permit Start Date
[/TD]
[TD="width: 123, bgcolor: transparent"]Permit Finish Date
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2/11/2019
[/TD]
[TD="bgcolor: transparent, align: right"]3/11/2019[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1/11/2019[/TD]
[TD="bgcolor: transparent, align: right"]12/11/2019
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]9/11/2019[/TD]
[TD="bgcolor: transparent, align: right"]10/11/2019
[/TD]
[/TR]
</tbody>[/TABLE]


Thanks for your help

 

[Toadstool]

Hi salsy,

You have three colors so you'll need three rules. The red, green, purple rules are:

=AND($A2 <> "",$A2 < TODAY(),$B2 < TODAY())

=AND($A2 <> "",$A2 <= TODAY(),$B2 >= TODAY())

=AND($A2 <> "",$A2 > TODAY())