Excel Date Display?

[Naveeddil]

Hello
Let's Suppose i gave some date 01-01-2007 in cell A1 and 01-05-2010 in cell b1

if i Subtract Cell B1 from A1 so the Answer will be 1100 Days BUT

i want these days as date like 6 days 1 months and 3 Years

OR

01/06/03


Is it Possible ?
Please Help

 

[Mike H]

Try this with the earlier date in A1

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

For an explanation of datedif look here
http://www.cpearson.com/excel/datedif.aspx

and for the occasions when it can go wrong look her
http://www.microsoft.com/office/com...64766f-755d-4739-8a8c-0a2461bbd23f&sloc=en-us

Mike

 

[Ron Rosenfeld]

Hello
Let's Suppose i gave some date 01-01-2007 in cell A1 and 01-05-2010 in cell b1

if i Subtract Cell B1 from A1 so the Answer will be 1100 Days BUT

i want these days as date like 6 days 1 months and 3 Years

OR

01/06/03


Is it Possible ?
Please Help

It is possible but difficult because of the variable numbers of days in a month
and in a year. Days and Weeks are more precise

So you need to be very specific on what you require.

For example, the formula Mike posted, which usually works, sometimes doesn't:

31-Jan-2008 29-Feb-2008 --> 0 years 0 months 27 days
Should be 29 days

31-Jan-2007 28-Feb-2007 --> 0 years 0 months 25 days
Should be 25 days

29-Feb-2008 1-Mar-2008 --> 0 years 0 months 3 days
This should be one (1) day

If you just need approximations, then the formula probably works most of the
time, especially if your starting date is not at the end of the month.
--ron

 

[Mike H]

Ron,

I agree and gave a link to a thread that pointed out those anomolies but the
real point for me is why anyone wants to include months in an age calculation
in the first place because the varying length makes it a meaningless number.

I may be wrong but my guess is that Microsoft never documented datedif in
Excel because it's quite the silliest function they ever produced.

Mike

 

[Ron Rosenfeld]

Ron,

I agree and gave a link to a thread that pointed out those anomolies but the
real point for me is why anyone wants to include months in an age calculation
in the first place because the varying length makes it a meaningless number.

I may be wrong but my guess is that Microsoft never documented datedif in
Excel because it's quite the silliest function they ever produced.

Mike

Well, it can certainly give inconsistent results.

--ron

 

[Fred Smith]

You may be right Mike, but it still doesn't absolve Microsoft. As you can
see by the post, people want to express age differences in months. You see
it on TV all the time. By producing the function, Microsoft was simply
responding to customer demand. If they are going to produce it, they should
document it.

Regards,
Fred.

 

[daddylonglegs]

Hello Ron,

For the 3 examples you gave Mike's formula gives me 29, 28 and 1 day
respectively which appear to me to be correct, although I think you get odd
results if start date is 31st January and end date 1st March.....in which
case try this formula

=YEAR(B1)-YEAR(A1)-(TEXT(B1,"mmdd")< TEXT(A1,"mmdd"))&" years
"&MOD(MONTH(B1)-MONTH(A1)-(DAY(B1)< DAY(A1)),12)&" months
"&B1-MIN(DATE(YEAR(B1),MONTH(B1)-(DAY(B1)< DAY(A1))+{1,0},DAY(A1)*{0,1}))&"
days"

 

[Ron Rosenfeld]

Hello Ron,

For the 3 examples you gave Mike's formula gives me 29, 28 and 1 day
respectively which appear to me to be correct, although I think you get odd
results if start date is 31st January and end date 1st March.....in which
case try this formula

Those are odd results, since I get something quite different. Something
strange is going on.

Obviously you didn't copy and paste your results, since Mike's formula gives a
string.

Here is what I get -- copied and pasted:

A B C
31-Jan-2008 29-Feb-2008 0 years 0 months 27 days
31-Jan-2007 28-Feb-2007 0 years 0 months 25 days
29-Feb-2008 1-Mar-2008 0 years 0 months 3 days

With Mike's formula, also copied and pasted (but dragged down from C1):

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

It'll be very interesting if this function gives different results in different
versions of Excel. I'm using Excel 2007

I think you get odd results if start date is 31st January and end date 1st March

Here's what I get using Mike's formula:

31-Jan-2008 1-Mar-2008 0 years 1 months 1 days

That seems like a perfectly reasonable answer. And it is also the same as the
answer I get using your formula.

--ron

 

[Bob I]

Hello,

You may not subtract the later date from an earlier date. It will make a
negative number.