I have two dates: 04/16/04, and 06/20/05.
I want to get the difference of time between the two in mm/dd/yy.
I subtracted 04/16/04 from 06/20/05 and got the answer of 03/05/01,
which would mean 3 months, 5 days, 1 year.
Shouldn't it be correctly stated as 2 months, 4 days, 1 year?
What have I done wrong?


--
lsmft
------------------------------------------------------------------------
lsmft's Profile: http://www.excelforum.com/member.php...o&userid=30678
View this thread: http://www.excelforum.com/showthread...hreadid=538058

03/05/01 is actually 03/05/1901.

If you format you cell to numbers you should get 430


--
MattShoreson
------------------------------------------------------------------------
MattShoreson's Profile: http://www.excelforum.com/member.php...fo&userid=3472
View this thread: http://www.excelforum.com/showthread...hreadid=538058

In A1, put 38093
In A2, put 38523
In A3, put =DATEDIF(A1,A2,"y") & " years, " & DATEDIF(A1,A2,"ym") & "
months, " & DATEDIF(A1,A2,"md") & " days"

In order to see:

4/16/2004
6/20/2005
1 years, 2 months, 4 days

--
Gary''s Student


"lsmft" wrote:


I have two dates: 04/16/04, and 06/20/05.
I want to get the difference of time between the two in mm/dd/yy.
I subtracted 04/16/04 from 06/20/05 and got the answer of 03/05/01,
which would mean 3 months, 5 days, 1 year.
Shouldn't it be correctly stated as 2 months, 4 days, 1 year?
What have I done wrong?


--
lsmft
------------------------------------------------------------------------
lsmft's Profile: http://www.excelforum.com/member.php...o&userid=30678
View this thread: http://www.excelforum.com/showthread...hreadid=538058

03/05/01 is actually 03/05/1901.

To work out how many e.g. days you'll have to use
=DAY(cellref)-day(cellref2)


Not sure why you would want to do though as 2days, 4 months and a year
wont equate to 430 days, (the difference between the two).


--
MattShoreson
------------------------------------------------------------------------
MattShoreson's Profile: http://www.excelforum.com/member.php...fo&userid=3472
View this thread: http://www.excelforum.com/showthread...hreadid=538058

XL stores dates as integer offsets from a base date. When you subtract a
date from another date, you get the number of days as a result, but XL
is stupid enough to think that the result should be formatted as a date.
If you format the result as general, you'll get the number of days.



In article ,
lsmft wrote:

I have two dates: 04/16/04, and 06/20/05.
I want to get the difference of time between the two in mm/dd/yy.
I subtracted 04/16/04 from 06/20/05 and got the answer of 03/05/01,
which would mean 3 months, 5 days, 1 year.
Shouldn't it be correctly stated as 2 months, 4 days, 1 year?
What have I done wrong?

Mattshoreson,
The dates were April 16, 2004 and June 20, 2005, hence the 2 months/4
days/ 1 year.
Thanks for your help.

Gary"s Student, you formula did exactly what I needed. Thanks to all of
you for your help.


--
lsmft
------------------------------------------------------------------------
lsmft's Profile: http://www.excelforum.com/member.php...o&userid=30678
View this thread: http://www.excelforum.com/showthread...hreadid=538058