Category: dates

Topic

New Reply

I have a column filled with dates. For some reason, Excel is taking the last zero off of many of my entries (e.g., 2/10 becomes 2/1). So I tried formatting that column all to Dates as “3/14”. However, that simply changed all of my entries to read 1/1. Why?

Regards,

JMT

Reply | Quote

Replies

Is the behavior a machine issue, an excel issue or a workbook issue?

If it is a workbook issue could you attach the offending file (deleting all proprietary info)

Steve

Reply | Quote

I have maybe the explanation of this strange behavior.

Excel uses numbers to represent the dates: 1900-01-01 is 1, 1900-01-02 is 2, 2010-01-31 is 40,209. The time is a fraction of a unit, so 2010-01-31 18:00 is internally represented as 40,209.75. If you have in Tools > Options > Calculation the option “1904 system” checked, then Excel considers 1904-01-01 as 1, 2010-01-31 is 38,747.

If you enter a formula like =2/10 and you format the cell as a date, Excel will return a date equals to the number resulting from the formula (in my example, 0.2) and will transform it to a date. If you have in Tools > Options > Calculation the option “1904 system” checked, then Excel will display the date corresponding to 0.2 which is January 1st, 1904, 04:48, or if you have formatted the cell as “m/d”, 1/1.

Reply | Quote

Stephane: I don’t have the 1904 date-system checked.

Sdccaper: I’m not sure if its Excel, Windows, or the machine. I will try to see if I can post a spreadsheet that has similar activity.

Reply | Quote