Quote:
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.
Try:
=MONTH(M2)-MONTH($B$79)
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.