MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Who can solve this teaser cos i'm going mad


Posted by Need Help with Dates on November 13, 2001 7:32 AM

this is really annoying me ....

I have two dates in A1, B1
01/01/1998 01/12/1998
I want C1 to contain 0 days 11 months 00 years

basically a1 is start date and b1 would be todays date and c1 would be the length of time u have been at the company??

please helppppppppppp i'm going mad


Posted by Aladin Akyurek on November 13, 2001 7:41 AM

=DATEDIF(A1,B1,"y")&" years, "&DATEDIF(A1,B1,"ym")&" months, "&DATEDIF(A1,B1,"md")&" days"

Aladin


Posted by Juan Pablo on November 13, 2001 7:44 AM

=(DAY(B1)-DAY(A1)) & " days " &(MONTH(B1)-MONTH(A1))&" months " &(YEAR(B1)-YEAR(A1))&" years"

If needed, format as General.

Juan Pablo

Posted by read this and thanks for answering b4 guys on November 13, 2001 7:47 AM

But does this account for leap years?????


Posted by Barrie Davidson on November 13, 2001 7:53 AM

Aladin, question for you


I keep forgetting about the DATEDIF function. My question, hopefully you'll know the answer, is: I cannot find this function in the function wizard or in help and yet your formula works when I paste it in to a test workbook. Any idea why I can't "find" it in Excel? Am I being clear with my question?

Confused,
Barrie

Posted by Aladin Akyurek on November 13, 2001 8:58 AM

I don't remember coming across any info concerning leap years. If in doubt, use Juan's formula that is built with the usual functions.

Aladin

========


Posted by Aladin Akyurek on November 13, 2001 9:02 AM

Re: Aladin, question for you

Barrie --

Its origins appears to be Lotus 1-2-3. Seemingly it's provided for compatibility reasons. As you discovered, there is no info to be found in Excel that reveals its existence.

Regards,

Aladin

===========

Posted by Barrie Davidson on November 13, 2001 9:42 AM

HOLD ON A MINUTE....DATEDIF provides a weird result


You can't use Juan's formula if the days/months result in a negative. For example, Start Date = Nov 15, 1998 and End Date = Mar 3, 1999.

Now the weird part. Interestingly, the DATEDIF function returns 0 years, 3 months, 16 days. Now there are 15 days from November 15 to the 30 and three days from March 1 to March 3. This should result in 18 days in total. Am I losing it here or is this correct?

The following formula (though very long) will give the result 0 years, 3 months, 18 days, assumes Start Date in A1 and End Date in B1.

=YEAR(B1)-YEAR(A1)+MIN(0,SIGN(MONTH(B1)-MONTH(A1)))&" years, "&MONTH(B1)-MONTH(A1)+MIN(0,SIGN(DAY(B1)-DAY(A1)))+MIN(0,SIGN(MONTH(B1)-MONTH(A1)))*-12&" months, "&IF(DAY(B1)-DAY(A1)<0,EOMONTH(A1,0)-A1+DAY(B1),DAY(B1)-DAY(A1))&" days"

Regards,
Barrie

PS - I'm open to improvements to the formula!Barrie Davidson

Posted by KEVIN on November 13, 2001 10:08 AM

Good catch Barrie

You could also insert an IF statement into your formula to get the desired result, for example instead of:

(DAY(B1)-DAY(A1))&" days "

you could use:

IF((DAY(B1)-DAY(A1))&" days "<0,(1-(DAY(B1)-DAY(A1)))&" days ",(DAY(B1)-DAY(A1))&" days ")

Posted by Kevin on November 13, 2001 10:20 AM

Re: Good catch Barrie

That formula should read:

IF((DAY(B1)-DAY(A1))&" days "<0,(365-(DAY(B1)-DAY(A1)))&" days ",(DAY(B1)-DAY(A1))&" days ")

Sorry

Posted by Barrie Davidson on November 13, 2001 10:53 AM

Re: Good catch Barrie

Kevin, thanks for the input. Two problems I see with this (sorry, I'm not trying to sound snotty).

1. Leap years have 366 days.
2. This formula is longer than mine.

What do you think?

Regards,
BarrieBarrie Davidson

Posted by Aladin Akyurek on November 13, 2001 1:36 PM

DATEDIF & Leap Years

Just found some info at:

http://www.cpearson.com/excel/datedif.htm

Aladin

=========

Posted by Barrie Davidson on November 13, 2001 1:46 PM

Aladin, have a look at "Hold On A Minute" above

Posted by Mark W. on November 13, 2001 2:21 PM

This works...

=INT(YEARFRAC(A1,A2,0))&" years, "&INT(YEARFRAC(A1,A2,0)*12)&" months,"&MOD(YEARFRAC(A1,A2,0)*360,30)&" days"

Posted by Mark W. on November 13, 2001 2:25 PM

Oops! No, it doesn't... back to the drawing board (nt)

Posted by Omar Sivori on November 13, 2001 2:42 PM

DATEDIF anomalies

Even without leap years, because of the varying number of days in each month, I do not think it's feasible to come up with a worksheet formula that removes all anomalies for all possible date combinations.

For instance, if there is a start date of 11-29-00 and an end date of 2-28-01, the result with a basic DATEDIF formula is 2mths 30days. It could be argued, however, that a more logical result is 3mths 2days.
So let's say that a new formula is created (or even a UDF) that will produce the result 3mnths 2days.
If we then look at a start date 11-29-00 and end date 2-27-01, let's say that both formulas would produce 2mths 29days which is OK.
If 1 day is added to the end date, however, the revised formula result would change from 2mnths 29days to 3mths 2 days which could be argued is an illogical change in the date difference.

In the abscence of a solution that doesn't remove all anomalies, I think it's just a matter of living with them but being aware of them

Posted by Barrie Davidson on November 13, 2001 2:50 PM

Re: DATEDIF anomalies

: this is really annoying me ....

Omar, wouldn't you agree that a start date of Nov 29, 2000 and an end date of Feb 27, 2001 is 2 months and 28 days (not 29)? Here's my logic:

Nov 29 to Nov 30 is 1 day
Feb 1 to Feb 27 is 27 days
Total is 28 days.

My formula posted in "HOLD ON A MINUTE" above correctly calculates this.

What do you think?

Regards,
BarrieBarrie Davidson

Posted by Mark W. on November 13, 2001 3:09 PM

Here's the definitive Microsoft link on this topic...

http://support.microsoft.com/support/kb/articles/Q95/9/48.ASP?LN=EN-US&SD=gn&FR=0&qry=dates&rnk=23&src=DHCS_MSPSS_gn_SRCH&SPR=XLW97

: this is really annoying me ....

Posted by Omar Sivori on November 13, 2001 3:11 PM

Re: DATEDIF anomalies

: So let's say that a new formula is created (or even a UDF) that will produce the result 3mnths 2days. : If we then look at a start date 11-29-00 and end date 2-27-01, let's say that both formulas would produce 2mths 29days which is OK. : If 1 day is added to the end date, however, the revised formula result would change from 2mnths 29days to 3mths 2 days which could be argued is an illogical change in the date difference. :


Yes, but ...
Add 1 day to the end date and the result becomes 2mnths 29 days which is also OK.
Then add another day to the end date (making it 3-1-01) and the result is 3 mnths 3days. So by adding one day to the end date, the date difference has increased by more than one day - which could be said is not OK.

It is not possible to eliminate all of these sort of anomalies. If you eliminate an anomaly for a particular set of dates, it will probably create a different anomaly for that situation and/or for some other situation.

Posted by Omar Sivori on November 13, 2001 3:17 PM

Amendment .....


AMENDMENT :-
ACTUALLY THIS IS NOT OK - IT COULD BE SAID THAT A MORE LOGICAL RESULT IS 3MNTHS 2DAYS Then add another day to the end date (making it 3-1-01) and the result is 3 mnths 3days. So by adding one day to the end date, the date difference has increased by more than one day - which could be said is not OK. It is not possible to eliminate all of these sort of anomalies. If you eliminate an anomaly for a particular set of dates, it will probably create a different anomaly for that situation and/or for some other situation.

Posted by Omar Sivori on November 13, 2001 3:24 PM

Which does eliminate the anomalies (nt)

Posted by Omar Sivori on November 13, 2001 3:25 PM

Meant to say ;- Which does NOT eliminate the anomalies (nt)

Posted by Kevin on November 14, 2001 6:46 AM

Re: Good catch Barrie

You bring up valid points - not sure how I would solve the leap year issue, and I guess my formula is a little more cumbersome. Can't blame a guy for trying right? :)

Thanks,
Kevin