Date Calc has me baffled

[xlhelp]

If I take a significant event such as 22 Nov 63 and try to find the difference between the event and TODAY() using the straight subtraction I get 39 06 13 (YMD)and get the same result using TEXT(value,"YY MM DD&quot. Then, using 365.25 days per year and 30.4375 days per month I get 39 05 12 (and of course, I realize this is not accurate); and...using DATEDIF, I get 39 05 13 which is what the old pen and paper says as well.

What I don't understand is why the difference in calc? Anyone have any suggestions?

Just in case you are wondering, my students picked the date.

Thanks.

"10 kinds of people in the world. Those who understand binary numbers and those who don't."

 

[Zathras]

The difference between two dates is the number of days between them:

(May 5, 2003) - (Nov 22, 1963) = 14,409 days

The number 14409 when formatted as a date comes out June 13, 1939 because Excel formats the result as a date, which it really shouldn't, but that's Microsoft for you.

 

[combo]

Hi Geoff,
I guess you know the answer - it is 39 years, 5 months (not may) and 13 days (of june).

Congrats on being voted tipmaster of the week!

combo

 

[xlhelp]

combo, Geoff definitely desrves the praise, however, he is xlbo and me I am just plain old xlhelp

"10 kinds of people in the world. Those who understand binary numbers and those who don't."

 

[SkipVought]

Hi,

The DIFFERENCE is not a date, it is a number of days -- happens to be 14,409 days, (which could also be a date serial number and if it were, would be 13 June 1939)

You have to deal with 14,409 days and convert that to years, months and days.



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com