Forum Discussion
Conditional Formatting for long-term dates
I assume dates in your sample are the dates when previous training was taken. In header column you mention within the text is that annual training or not. If not when we assume that's once per 3 years training.
Thus we try to find in header cells if that's annual training or not. If annual when formula
IF( ISNUMBER( SEARCH("annual", A$1) ), 12, 36 )returns 12 (months), otherwise 36.
Finally
EDATE(A2, 12) or EDATE(A2, 36)returns the date which is 12 or 36 months later than the date in A2. Other words Due Date.
With that
rule formula is
=(A2<>"")*( EDATE(A2, IF( ISNUMBER( SEARCH("annual", A$1) ), 12, 36 ) + $H$1 ) < TODAY() )I would not have come up with that in a long time! Thank you for showing it is possible.
However, how do I adjust the formula so that the cell is:
- Red if today is past due date
- Amber if today is within 90 days of due date
- Green if today is over 90 days of due date
I don't understand how the individual components work to put them together do it.
Could you help again please?
You need as many rules as different formats you'd like to apply. In this case 3 rules. Due Date we calculate as
EDATE(A2, IF( ISNUMBER( SEARCH("annual", A$1) ), 12, 36 ) )
when 3 rules for
DD < today (red)
DD >= today+90 (green)
DD >= today
Rules shall be in above order, conditional formatting applies colors for the first rule which returns TRUE.
Finally
- Hi Sergei
Thanks for another helpful response. Can I push my luck and ask what the 12 and 36 are for in this line?
EDATE(A2, IF( ISNUMBER( SEARCH("annual", A$1) ), 12, 36 ) )
Thanks again
Lois
