Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Time between two dates
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! |
#2
|
|||
|
|||
Hi!
Can I interpret that as 17 years, nine month and 17 days? 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 "FA" wrote in message ... 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! |
#3
|
|||
|
|||
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 Replace@mailinator with @tiscali.co.uk "FA" wrote in message ... 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! |
#4
|
|||
|
|||
FA wrote:
I want to be sure how long has been since 1/18/88 to 9/30/05. 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. |
#5
|
|||
|
|||
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding time | Excel Discussion (Misc queries) | |||
help with date and time | Excel Worksheet Functions | |||
unmet challenge | Excel Worksheet Functions | |||
Time formatting | Excel Discussion (Misc queries) | |||
entering numbers to display a time format | Excel Discussion (Misc queries) |