Handling date formats...

M

Morgs

SO I know all about using the custom option in format cells, but this only
works if I have something in a format excel recognizes, right?

I have lists of dates printed out in yyyymmdd format, and I want that to be
converted to dd/mm/yyyy. How do I do this?

Thanks,
Morgs
 
P

PCLIVE

Here's a clunky way you could do it. In another column you could use this
formula and copy it down as needed.
=TEXT(DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2)),"dd/mm/yyyy")

or

=RIGHT(A1,2) & "/" & MID(A1,5,2) & "/" & LEFT(A1,4)


HTH,
Paul
 
R

Rick Rothstein \(MVP - VB\)

Here's a clunky way you could do it. In another column you could use this
formula and copy it down as needed.
=TEXT(DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2)),"dd/mm/yyyy")

or

=RIGHT(A1,2) & "/" & MID(A1,5,2) & "/" & LEFT(A1,4)
Click to expand...

Or even... =TEXT(TEXT(A1,"0000-00-00"),"dd/mm/yyyy")

Rick
 
D

Dave Peterson

If those values are in a single column, you can use:
Data|Text to columns|fixed width (but don't draw any lines)
and choose ymd as the date format.

After you finish that step, you can apply the date format you want to make it
look pretty.

If you have multiple columns, you have to do each separately.
 
Top