Excel Date conversions

  • Thread starter Thread starter Gerri
  • Start date Start date
G

Gerri

How do I set up a column to automatically convert numbers
(example - 01012003) into a date format (example -
01/01/2003). I tried doing this by format, cells, date -
then entered the number and the date came up different.
 
You have to either use a date format recognized by Excel or use a helper
cell/column to extract and concatenate the string to appear as a date:
=LEFT(A1,2)&"/"&MID(A1,3,2)&"/"&RIGHT(A1,4).
 
Excel will attempt to recognize a date when you enter it,
but it has its limitations. In your case, you can do a
couple of things:

1) Download and adapt some code from MVP Chip Pearson's
website that when convert your entries automatically:

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

2) Use formulas already in place to convert the date in
another column. Format your entry cells as text and then
use the formula:

=REPLACE(REPLACE(A1,3,,"-"),6,,"-")*1

Format the formula column as date.

HTH
Jason
Atlanta, GA
 
Back
Top