date dif issues

Hi,
i'm new to the forum and boy, am i frustrated...ahem...
the issue i'm having is with DATEDIF in Excel 2003.
i have been using the above function for some time in a spreadsheet i built myself.
i have just inherited another spreadsheet and cannot get it to work.
the formula i'm using is =DATEDIF(B79,M2,"m") where B79 is toadys date and M2 is 30 Apr 11. this is showing "1" which is correct as there's 1 months difference.
in all the other cells, and the M2 cell has been changed to relate to a different date, i'm getting a long number. for example; if the second date is 24 Nov 11 i'm getting 1342.
i assume this is something to do with 1900 but any help would be greatly appreciated.
Cheers,
Al

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?

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.