conditional formatting with multiple formulas

[toolboxx]

hi all. i have an xls training matrix sheet where the source data consists of expiry dates corresponding to peoples names. these names are are then repeated at the top of the sheet and using conditional formatting I have a traffic light system of green when they are within date, amber within 30 days, and red when overdue

so…… my question is with the formula based on minussing a future date from today(), when there is no source data, such as a person has not done a specific course… I have tried the conditional formatting makes it red, so it appears it is over due when really it isnt… what I would like is for the cell to stay white, if there is no value input but with the flexibility of when data is input the traffic lights come into play…I have used the if(x1=0,””, x1-x2) which has worked on previous sheets but not with this one..

help please!

 

[milesUK]

toolboxx, you don't say which version you are using but xl2007 allows to stop further pocessing of conditional formatting once a match is found.

With that you could look for the result {of say "=0-TODAY()" which would yeild a large -ve number ie<-9999} and format accordingly if necessary. Tick the box to prevent further matches. If this first condition is not met then CF continues to format the cell as traffic lights.