Date and Mileage alerts

[NoelD]

Hi All

I am seeking help in creating some additional conditional formatting on the attached spreadsheet.

I have an "ALERT" traffic light "RED" when the current mileage exceeds the Scheduled Mileage. It is "AMBER" if with 1500 km and GREEN when outside this.

I would like to incorporate the Scheduled Date versus TOday into the RED alert; an AMBER alert when it's 30 days away from the Schedule date and GREEN outside of these dates.

Is this possible in the one ALERT column

Regards

Noel

​

 

[jasonb75]

You should be able to do it by combining the formulas in columns F and G.
Quite how you would need to combine them, or if any changes are needed would depend on the formulas that you're using.

 

[NoelD]

Column F is Conditional Formatting


and

and Col G has this formula

=IF($G$1>[@[Auto Calculated
Next Service Date]],"RED",IF($G$1-$I$1<[@[Auto Calculated
Next Service Date]],"AMBER","GREEN"))

I am at a loss as to how to combine or if there is a better formula to use for the task

 

[jasonb75]

Column F will have a formula as well, changing it to this should work

=MIN(IF($G$1>[@[Auto Calculated Next Service Date]],1,IF($G$1-$I$1<[@[Auto Calculated Next Service Date]],1,3)),
IF([@[Current Mileage]]>[@[Auto Calculated Next Service Due]],1,IF([@[Current Mileage]]+1500<[@[Auto Calculated Next Service Due]],1,3)))

Conditionally format as >=3, green. >=2 amber, <2, red.

 

[NoelD]

Thanks for looking into this formula. I entered it on my spreadsheet but this is the error I am getting

 

[jasonb75]

They all look correct, I've based the formula on the table heading names. I can't do any more without the original formula from F3.

 

[NoelD]

Thanks, have it sorted now