Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

Mizugori

Programmer
May 21, 2007
56
US
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?
 
Format the cell as a number

[Blue]Blue[/Blue] [Dragon]

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



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]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 



FYI...
faq68-5827

Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
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
 



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]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top