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.
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
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. 🙂