Time between two dates

F

FA

I want to be sure how long has been since 1/18/88 to 9/30/05. If I substract
the numbers it says 9/12/17. Can I interpret that as 17 years, nine month and
17 days? It just that it looks as the answer should be 8 months and not
nine.

Please help.
Thank you!
 
B

Biff

Hi!
Can I interpret that as 17 years, nine month and 17 days?
Click to expand...

Actually, NO!

The result you see is due to Excel being "helpful" and using the DATE format
from the source cells and applying it to the formula cell.

The true result of the formula is 6465 days. When formatted as DATE, 6465 is
equivalent to the date of 9/12/1917.

Format the formula cell as GENERAL.

Biff
 
S

Sandy Mann

Yes if should. There is an undocumented (apart form XL200 I believe)
function called DATEDIF. With the start date in E1 and the end date in F1
try:

For years: =DATEDIF(E1,F1,"y")
For months: =DATEDIF(E1,F1,"ym")
For days: =DATEDIF(E1,F1,"md")

--
HTH

Sandy
[email protected]
Replace@mailinator with @tiscali.co.uk
 
J

joeu2004

FA said:
I want to be sure how long has been since 1/18/88 to 9/30/05.
Click to expand...

There could be many ways. To compute the number of days:

DATE(2005,9,30) - DATE(1988,1,18)

Converting the actual number of days (6465) back to years,
months and days can be misleading because of the vernacular
(and legal) use of the terms -- and because any such
conversion relies on averages (365 days/yr, 30 days/mo).

You might think of 6465 days as 17y 8m 20d. But in vernacular
and legal terms, it is 17y 8m 12d.

(Jan 18 1988 to Jan 18 2005 is always "17 years", no matter
how many days transpired. Jan 18 to Sep 18 is always
"8 months", no matter how many days transpired. Sep 18 to
Sep 20 of the same year is 12 days.)

If you want the latter, the DATEDIF() solution is the one
you want.
 
R

Ron Rosenfeld

I want to be sure how long has been since 1/18/88 to 9/30/05.
Click to expand...
You might think of 6465 days as 17y 8m 20d. But in vernacular
and legal terms, it is 17y 8m 12d.
Click to expand...

That still depends on some conventions.

For example, between those dates there are 17y 8m (or 212m) if one counts full
calendar months (Feb 1988 - Sep 2005 inclusive); but then there are another 13
days in January (31-18) which would give a result of 17y 8m 13d.




--ron
 
Top