Date format

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

When I enter the date format of choice 051607 and hit the return tab I get
this format 17-Apr-45

How could I fix this format, I have over 300+ days to correct
 
Are sure you don't get 16-Apr-41?

The reason this is happening is that dates are really just numbers formatted
to look like dates. The dates are measured in increments of 1 since a base
date. The default base date is Jan 1 1900 so its value is 1. Jan 2 1900 has
a value of 2. Jan 1 2007 has a value of 39083. It's the 39083rd day since
the base date.

So, when you enter this into a cell: 051607

Excel ignores the leading 0 and the value of the cell is numeric 51607. If
you have the cell formatted as a DATE then you will see 16-Apr-41 which is
actually 16-Apr-2041 or the 51607th day since the base date of Jan 1 1900.

So, you can't enter "dates" like that. There is some VBA code that will let
you do this and convert the numeric entry to the correct date:

http://cpearson.com/excel/DateTimeEntry.htm

If you have a bunch of cells with these types of entries:

051607
052207
040307

And you need to convert them to dates, try this:

Select the range of cells in question
Goto the menu Data>Text to Columns
Click Next, Next
In step 3 select DATE (and the format of your choice)
Finish

Biff
 
Back
Top