Conditional Formatting/Formulas

Question

Hi,

Column A - Calibration date

Column B - Calibration due date (using the formula EDATE)

I would like column B to turn green when more than 6 months, orange when less than 3 months away and red when overdue, can someone help me with the conditional formatting please?

Thank you

Kylie

Answer 1

Select B3:B100 or however far down the data go.

On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Format only cells that contain'.
Leave the first drop down set to 'Cell Value'.
Select 'greater than' from the second drop down.
In the box next to it, enter the formula

=EDATE(TODAY(), 6)

Click Format...
Activate the Fill tab.
Select green as fill color.
Click OK, then click OK again.

Repeat, but with 'less than or equal to' and

=EDATE(TODAY(), 3)

and orange as fill color.

Finally, repeat with 'less than' and

=TODAY()

and red as fill color.

Remark: cells with dates between 3 and 6 months from today will not be colored.

Answer 2

Hi,

Sorry

I would like:-

Cell B3 to be green if todays date is more than 6 months away

Cell B3 to be orange if todays date is equal to less than 3 months away

Cell B3 to be red if todays date past

Thank you

Answer 3

What exactly do you mean by "green when less than 3 months, orange when over 6 months away"?