Reverse and format string
 

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

Reverse and format string
 

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

Format as Date

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Mike" wrote in message ...
|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

Reverse and format string
 

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


"Mike" wrote:

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

Reverse and format string
 

Without formulas and helper cells.

DataText to ColumnsNextNextColumn Data FormatDateYMDFinish.

Format to your liking.


Gord Dibben MS Excel MVP

On Tue, 31 Oct 2006 03:56:01 -0800, Mike wrote:

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