stop Excel from reformating my dates

**LuAnn** · 12-07-2007, 10:55 PM

I am working on a project with a number of other people. They are entering data in Microsoft Word and I am converting it to Excel and merging all the data in one spreadsheet. The problem I'm having is the way Excel takes the dates we enter and changes them. We enter the dates as dd mmm yyyy. As long as the date is pre-1900 it stays that way. But if the date is post 1900, such as 12 Mar 1920, Excel changes it to 12/Mar/20 in the cell (3/12/1920 in the formula bar). I can't just click on the column and reformat as text because then I wind up with 7377. I have no idea how it comes up with that number. I just want the dates to stay the way we enter them. Is there anything I can do? I do not want to manually change them all.

BTW, I'm using Excel 2000.

Thanks.

**mikerickson** · 12-07-2007, 11:06 PM

Welcome to the forum,

12 Mar 1920 was the 7377th day of the last century. That's the way that Excel stores dates. The number 7377 is what is stored in the cell, and the Number Formatting turns it into "03-12-1920". (I have a different default than you.) So all you have to do is apply your own custom format.

Select a cell and Format Cells, Number tab, Category=Custom

Enter dd mmm yyyy in the dialog box and the cell should show things the way you like. Then use the format painter to apply the formatting to all the cells you want with that format.

**LuAnn** · 12-07-2007, 11:14 PM

Thank you. That works. But why does it still show up in the formula bar as 3/12/1920? And why is 1900 the cut off date for having it entered as text?

**mikerickson** · 12-07-2007, 11:22 PM

Why 1900? Because MicroSoft says so. You have to have a zero somewhere, Microsoft chose 1900. (If you are running a Mac, go into the Excel preferences, Calculation and turn the 1904 date system OFF.)

Actualy, I think it is MicroSoft's tribute to the old National Bureau of Standards' definition of "second" as a fraction of the year 1900.

Similarly for the formula bar.
Formatting can hide details. You could have formatted the cell MMM YYYY, so 28 Oct 1956 will show as "Oct 1956". The formula bar shows the full value in the cell, even if the cell formatting excludes some information. The formatting for the formula bar is not user controlable.

stop Excel from reformating my dates

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

stop Excel from reformating my dates

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions