OfficeFrustration

OfficeFrustration (http://www.officefrustration.com/index.php)
-   New Users (http://www.officefrustration.com/forumdisplay.php?f=7)
-   -   date dif issues (http://www.officefrustration.com/showthread.php?t=1123339)

aldawson March 16th, 2011 01:04 PM

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

aldawson March 16th, 2011 01:32 PM

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?

tarquinious March 16th, 2011 04:56 PM

Quote:

Originally Posted by aldawson (Post 3747703)
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.


All times are GMT +1. The time now is 09:52 AM.

Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
OfficeFrustration.com