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

[Mizugori]

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?

 

[bluedragon2]

Format the cell as a number

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[SkipVought]

Hi,

Excel is trying to be helpful. Since your formula as dates in it, Excel 'helpfully' formats the result of your formula as a date. Simply format the resulting range as GENERAL.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

FYI...
faq68-5827

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Mizugori]

i tried this but it does not work. if I format it as general number, it changes from 01/29/1900 to 01/29/00

 

[SkipVought]

01/29/00 is not a number. It is a FORMAT!

If 01/29/00 is what you see in the cell when it is formatted as GENERAL, then you do not have a numeric value in this cell!

I just verified this process.

I entered the formula.

Got 1/0/1900, which, by the way, has a VALUE of ZERO.

Changed the FORMAT in the cell to GENERAL.

See a ZERO displayed, which was the value in the cell all along!!!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]