Handling date formats...

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

Guest

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
 
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
 
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
 
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.
 
Back
Top