Having trouble with Date format.

[Barry Katcher]

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?

[Jack in the UK]

good question, like it suggest also post in 123 section on the baord as lurkes might just know in there.

[Brian from Maui]

Barry,

Have you checked under Tools/Options/1904

[Mark W.]

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 ]

[Barry Katcher]

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".

[Mark W.]

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.

[Brian from Maui]

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.

[Barry Katcher]

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".

[Mark W.]

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

[Mark W.]

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".