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)

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel date format YYYYMMDD 1
How do I change the date format? 3
Date Formats 1
Excel 2002: How to make the date format uniform ? 1
Date Seperator 2
Convert dates stored as text 2
date 1
Excel 2007 displayed dates 2

Back
Top