Date conversion

T

Tom

I'm sure this question has been asked before.
How to convert the date expressed as "20061215" to read as 15/12/2006?
Thanks for your help.

Tom
 
J

JMB

If your data is in A1, try

=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))

then format the cell as d/m/yyyy
 
R

Roger Govier

Hi Tom

One way
Click on cell(s), Data>Text to Columns>Next>Next>Date>YMD>Finish
 
T

Tom

Wow! Didn't know there are many other ways. However, which of your
suggestions also work the other way around, i.e. change 15/12/2006 back to
the number 20061215?
 
D

Dave Peterson

You could use a formula in another cell:

=text(a1,"yyyymmdd")

If you wanted to keep it a date, just change the way it looks, you could give it
a custom format of:

yyyymmdd
 
T

Tom

A clever formula. Thanks again, Dave.

Dave Peterson said:
You could use a formula in another cell:

=text(a1,"yyyymmdd")

If you wanted to keep it a date, just change the way it looks, you could
give it
a custom format of:

yyyymmdd
Click to expand...
 
Top