Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Having trouble with Date format.

  1. #1
    Board Regular Barry Katcher's Avatar
    Join Date
    Feb 2002
    Location
    Dog Beach, Florida. Yeaahh!
    Posts
    4,053
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I've formatted a column of cells with the "m/dd/yyyy" date format and, no matter what date I input, the cell returns "1/0/1900". This is in a workbook that I imported from Lotus 1-2-3, if that helps - when I format a new Excel workbook, the date works just fine. Can anyone help?

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,184
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    good question, like it suggest also post in 123 section on the baord as lurkes might just know in there.
    Free Excel based Web Toolbar available here.

    Jack in the UK
    J & R Excel Solutions
    "making Excel work for you"

  3. #3
    Board Regular Brian from Maui's Avatar
    Join Date
    Feb 2002
    Posts
    8,450
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Barry,

    Have you checked under Tools/Options/1904

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You'll get 1/0/1900 in a date formatted cell when you enter 0. By chance are you trying to enter a date as though it was a formula (e.g., =4/24/2002). =4/24/2002 produces 0.0000832500832500832 which is treated like 0 in a date formatted cell. This date should be entered as 4/24/2002 or ="4/24/2002"+0.

    For more on the entry of dates see the Excel Help Index topics for...

    1. Tips on entering dates and times
    2. How Microsoft Excel performs date and time calculations

    [ This Message was edited by: Mark W. on 2002-04-24 10:56 ]

  5. #5
    Board Regular Barry Katcher's Avatar
    Join Date
    Feb 2002
    Location
    Dog Beach, Florida. Yeaahh!
    Posts
    4,053
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Brian, I went into Tools-Option-Calculation and checked the "1904" box. All it did was change the "1/0/1900" to "1/1/1904".

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-24 10:56, Barry Katcher wrote:
    Brian, I went into Tools-Option-Calculation and checked the "1904" box. All it did was change the "1/0/1900" to "1/1/1904".
    See my posting above.

  7. #7
    Board Regular Brian from Maui's Avatar
    Join Date
    Feb 2002
    Posts
    8,450
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Barry,

    From the help file:

    n Microsoft Excel for Windows (and Lotus 1-2-3), days are numbered from the beginning of the century; the date serial number 1 corresponds to the date January 1, 1900. Microsoft Excel for the Macintosh uses the 1904 date system; the date serial number 1 corresponds to January 2, 1904. To change the date system for use in calculations, click Options on the Tools menu, and then click the Calculation tab. Under Workbook options, select the 1904 date system check box. Use the 1904 date system for a workbook if you use that workbook with other workbooks that use the 1904 date system.

  8. #8
    Board Regular Barry Katcher's Avatar
    Join Date
    Feb 2002
    Location
    Dog Beach, Florida. Yeaahh!
    Posts
    4,053
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Mark, I am entering as, for example, 5/1/1988 but the formula bar IS showing the entry as =5/1/1988, which calculates to a zero, which returns 1/0/0900. But, if I'm entering correctly, WHY is Excel adding the equal sign? I also tried your "5/1/1988"+0, but the same thing occurs-an = sign and "1/0/1900".

  9. #9
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Barry's problem has nothing to do with the selected date system!

  10. #10
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-24 11:12, Barry Katcher wrote:
    Mark, I am entering as, for example, 5/1/1988 but the formula bar IS showing the entry as =5/1/1988, which calculates to a zero, which returns 1/0/0900. But, if I'm entering correctly, WHY is Excel adding the equal sign? I also tried your "5/1/1988"+0, but the same thing occurs-an = sign and "1/0/1900".
    Choose the Tools | Options... menu command, click on the "Transition" tab, and uncheck "Transition formula entry".

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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