Results 1 to 4 of 4

Thread: Category: dates

  1. #1
    5 Star Lounger
    Join Date
    Aug 2006
    Posts
    784
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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


  3. #3
    Star Lounger
    Join Date
    Dec 2009
    Location
    Mexico City, D.F., Mexico
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.
    This eco-post is made of recycled electrons

  4. #4
    5 Star Lounger
    Join Date
    Aug 2006
    Posts
    784
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •