Originally Posted by aldawson
OK, sorted now but another problem is that if the date has gone "over", ie i get #NUM! in the box, i cannot conditional format that cell to go a different colour as the rest of the formatting works of months remaining???
I've only got 3 conditions at present so that as the months count down the cells change colour from green to amber then red. cannot seem to put another condition in?
Have you tried the MONTH function? It may work a little better, and gives you a negative value if the date is in the future instead of a "#NUM!" error.
If you have to stick with the DATEDIF, you could trap the error with something like:
=IF(ISERROR(DATEDIF(B79,M2,"m")),"Date in the past",DATEDIF(B79,M2,"m"))
Excel 2007 allows you to have more than 3 conditional formats, whereas Excel 2003 only allows the 3. You could always have the default colour say Blue, then apply the R, A, G conditional formatting. If none of these is true it will result in a Blue colour.