Date Subtraction

[Newbie]

I am trying to take a later date and subtract from it an earlier date to
determine the time in between. (07/11/06)-(04/14/05)=(03/28/01)
1Y 3M 28D is not correct. What am I missing?

 

[T. Valko]

See this:

http://www.cpearson.com/Excel/datedif.aspx

 

[JE McGimpsey]

You're assuming that 3/28/01 means 1Y 3M 28D...

XL stores dates as integer offsets from a base date (12/31/1899 in
Windows default 1900 date system, 1/1/1904 in the Mac default 1904 date
system), with 1 = 1 day.

Format your result cell as General and you'll see that the result is 453
days. Format it as a date, though, and XL incorrectly determines that
453 days after 12/31/1899 is 3/28/1901.

If you want years, months, and days, you can use DATEDIF:

http://cpearson.com/excel/datedif.aspx

Be aware, however, that months are impossible to generally and
consistently define algorithmically.

 

[Newbie]

Can you combine "y" "m" "d" in the formula to get 2 yrs 3 months and 5 days?

 

[Newbie]

Can I get y, m and d in each formula vs 35 months

 

[Ron Rosenfeld]

I am trying to take a later date and subtract from it an earlier date to
determine the time in between. (07/11/06)-(04/14/05)=(03/28/01)
1Y 3M 28D is not correct. What am I missing?

You are missing knowledge as to how Excel stores and formats dates.

Subtracting one date from another will give you the number of days between the
dates; if you format that value as a date, you get a date.

See http://www.cpearson.com/excel/datedif.aspx for more discussion and a
possible solution to your problem.
--ron

 

[JE McGimpsey]

You can't combine, but you can use

=DATEDIF(A2,A1,"y") & " yrs " & DATEDIF(A2,A1,"ym") & " months and "
& DATEDIF(A2,A1,"md") & " days"

However, DATEDIF calculates month length based on the month in the first
date, so, for instance:


A1: 3/1/09
A2: 1/31/07


will return

2 yrs 1 months and -2 days

 

[Shane Devenshire]

Hi,

What do you mean by vs 35 months? Keep in mind that months have different
number of days, so when you say 35 months which months are you talking about?