Date format

[Ward]

Hi,

I need some advice on a formula to reformat a Numbers in column A into a
Date format in column B i.e.

A B
19600321 changed to 21/03/1960
19850723 changed to 23/07/1985
19770707 changed to 07/07/1977

Regards,
Ward

 

[Jacob Skaria]

Cell A1 = RIGHT(A1,2)&"/"&MID(A1,5,2)&"/"&LEFT(A1,4)

If this post helps click Yes

 

[Ward]

thank you

Cell A1 = RIGHT(A1,2)&"/"&MID(A1,5,2)&"/"&LEFT(A1,4)

If this post helps click Yes

 

[Stefi]

Or, if you want an Excel date

=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))
and format it as you like.

Regards,
Stefi

„Ward†ezt írta:

 

[Ashish Mathur]

Hi,

You can also try this (no-formula) approach. Highlight A2:A4 and go to Data

Text to columns. Now click on Next two times. On the third screen select

the column data format as date and select YMD in the drop down. Select the
destination cell as B2 and click on Finish

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Ward]

Hi,
This way is working better for me because I don't have to multiply the new
text field by 1 and then chose date cell format it does it automatically

 

[David Biddulph]

=--TEXT(A2,"0000\-00\-00") and format appropriately

 

[Gord Dibben]

You could also use Data>Text to Columns>Next>Next>Column Data
Format>Date>YMD and finish.

No helper column needed.


Gord Dibben MS Excel MVP