Change Format

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

How can I change a cell with general formatting data to a
date format? As an example, I have in cell A1: 20040205
and I would like for it to read: 2004/02/05 (i.e., Feb 2,
2004)
 
Hi Steve
changing the format to DATE just goto 'Format - Cells' and choose your
date format. Though this will not change your entry '20040205' to a
valid date. You have to enter the dates with a delimiter or use a macro
whcih will do the conversion for you (see
http://www.cpearson.com/excel/DateTimeEntry.htm)

Frank
 
Steve

With cell(s) selected Data>Text to Columns>Next>Next>"Column Data
Format">Date.

Pick your format YMD or YDM

2004/02/05 is Feb 5, 2004 or May 2, 2004

Gord Dibben Excel MVP
 
It would be hard to get Feb 2, 2004 out of 20040205, but

Assume they are in a single column

select the column and do

Data=>Text to columns, select fixed, skip the second dialog, in the third,
select YMD and click OK.

Now format the selection using Format => Cells, Number Tab, custom
YYYY/MM/DD

assumes you wanted Feb 5, 2004. Adjust if it was May 2, 2004
 
Hi Steve,
Chip's page is for manually entering in different lengths of
numeric digits, but there is no subroutine on his page to
fix up existing data. It would be very similar though.

You can use a helper column before existing column B
=Date(year,month,dayofmonth)
=Date(left(a4,4), mid(a4,5,2), right(a4,2))

If you want to eliminate the original then
Select the new column
Copy (ctrl+B) the new column B
Edit, paste special, values
remove the original column A

If you see a sequential number instead of a date,
then Format, Cells, Date (or custom)
but most likely having used date function you won't have
to format anything.
 
Back
Top