Forum Discussion
Solved
Conditional Formatting for long-term dates
Hi all I want to conditionally format a sheet so that when training is coming due it will colour the cell a particular colour. So, if training should be renewed every 3 years I need the cell to sho...
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
LoisI 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.