A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

date dif issues



 
 
Thread Tools Display Modes
  #1  
Old March 16th, 2011, 02:04 PM
aldawson aldawson is offline
Member
 
First recorded activity by OfficeFrustration: Mar 2011
Posts: 2
Default 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
Ads
  #2  
Old March 16th, 2011, 02:32 PM
aldawson aldawson is offline
Member
 
First recorded activity by OfficeFrustration: Mar 2011
Posts: 2
Default

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?

Last edited by aldawson : March 16th, 2011 at 02:35 PM.
  #3  
Old March 16th, 2011, 05:56 PM
tarquinious tarquinious is offline
Experienced Member
 
First recorded activity by OfficeFrustration: Mar 2011
Posts: 34
Default

Quote:
Originally Posted by aldawson View Post
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.
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 04:23 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright 2004-2017 OfficeFrustration.
The comments are property of their posters.