Who can solve this teaser cos i'm going mad

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

Aladin

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

If needed, format as General.

Juan Pablo

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

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

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

========

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

===========

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

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 ")

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

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

DATEDIF & Leap Years

Just found some info at:

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

Aladin

=========

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

This works...

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

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

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

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

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 ....

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.

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.

Which does eliminate the anomalies (nt)

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

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