Having trouble with Date format.

Barry Katcher

Well-known Member
Joined
Feb 25, 2002
Messages
4,053
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?
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
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
 
Upvote 0
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".
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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".
 
Upvote 0
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".
 
Upvote 0

Forum statistics

Threads
1,213,533
Messages
6,114,179
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top