How do I change an 8 digit reversed date number into a date

[jrnbru59]

Example - to change 20090331 into 31/03/2009

 

[Sheeloo]

With the string in A1 use this in B1
=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))

 

[Kassie]

=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2)) will change it inot a date number,
which you can then format to show yyyy/mm/dd, or dd/mm/yyyy

--

HTH

Kassie

Replace xxx with hotmail

 

[T. Valko]

Try this...

Select the cell(s) you want to convert
Goto the menu Data>Text to Columns
Click Next twice
In step 3 of the wizard, under Column data format select Date
In the drop down select YMD
Finish

 

[Dave Peterson]

If you have a bunch of dates in a single column, you can:

Select that column
Data|Text to columns
Fixed width (don't include any lines!)
choose date and ymd
and finish up

Then format the range the way you like (dd/mm/yyyy).

If you have multiple columns you can use this technique on each of them.

 

[Rick Rothstein]

Assuming the date is in A1, use this formula in any other cell (B1 maybe)
and then Format that cell as a Date using dd/mm/yyyy...

=--TEXT(A1,"0000-00-00")