Forum Discussion
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.
Brilliant. Thanks! I shall give it all a go!
Lois_B , glad to help
- It's working perfectly! Days of slogging (then giving up) saved. 🙂
