trying to subtract dates, getting 1/29/1900 as result, please help!

Mizugori

Senior member
May 3, 2007
496
0
0
I am trying to subtract two dates in excel but I keep getting weird results...

My dates are formatted like this: 8/11/2010

I am using TODAY() to return the current date. Then I want to compare that to see how many days have passed since a given date. So my formula is this: =TODAY()-D2

(D2 is just a cell in my spreadsheet that has a date stored in it.)

The result I get is 01/29/1900. I was trying to get the number of days that have passed since a given date. For example, I have a maintenance schedule and I record service dates in one column, then I later want to be able to say 'ok how many days has it been since the last time I serviced this location?' and get a number like 45 days.

What am I doing wrong?
 

Binky

Diamond Member
Oct 9, 1999
4,046
4
81
By default, Excel counts the days between two dates. Either of the following formulas should result in 3,144 as of right now:

=today()-A1 (where A1 contains 1/1/2002)
=A2-A1 (where A1 contains 1/1/2002 and A1 contains 8/11/2010

Just format the result cell as a number...or you will get 8/9/1908, which is correct but formatted wrong.

All dates in Excel are stored as the number of days since 1/1/1900. They are just a number.