Reverse and format string

M

Mike

I receive date data in yyyymmdd format eg, 20061031 = 31 Oct 2006.
I want to transpose the data to dd-mm-yyyy format.
Prefer not to use VBA.
I thought I had seen an in cell format using the text() & mid() expressions
but cannot reproduce it.

Tks
 
N

Niek Otten

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

Format as Date

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I receive date data in yyyymmdd format eg, 20061031 = 31 Oct 2006.
| I want to transpose the data to dd-mm-yyyy format.
| Prefer not to use VBA.
| I thought I had seen an in cell format using the text() & mid() expressions
| but cannot reproduce it.
|
| Tks
 
J

Jim May

another way: in B1 enter
=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))
then format B1 - Custom dd-mm-yyyy
 
G

Gord Dibben

Without formulas and helper cells.

Data>Text to Columns>Next>Next>Column Data Format>Date>YMD>Finish.

Format to your liking.


Gord Dibben MS Excel MVP
 
Top