Date format
 

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

Date format
 

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

If this post helps click Yes
---------------
Jacob Skaria


"Ward" wrote:

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

Date format
 

thank you

"Jacob Skaria" wrote:

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

If this post helps click Yes
---------------
Jacob Skaria


"Ward" wrote:

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

Date format
 

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:

thank you

"Jacob Skaria" wrote:

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

If this post helps click Yes
---------------
Jacob Skaria


"Ward" wrote:

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

Date format
 

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

Date format
 

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

"Ashish Mathur" wrote:

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

Date format
 

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

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

Date format
 

You could also use DataText to ColumnsNextNextColumn Data
FormatDateYMD and finish.

No helper column needed.


Gord Dibben MS Excel MVP

On Fri, 27 Mar 2009 16:30:00 -0000, "David Biddulph" <groups [at]
biddulph.org.uk wrote:

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

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