Excel Convert yyyymmdd text to Date format 1

[nirod]

Importing text into Excel sets the yyyymmdd field as text format. Program will not convert this field to DATE format. Get ######. Cannot find help in Excel on this matter. Can anyone tell me how to convert 20060701 to 07/01/2006?

 

[AnotherHiggins]

In a column to the right of the imported data, use this formula:
[tab][COLOR=blue white]=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))[/color]
Copy the formula down as far as needed.

(Replace A1 with the appropriate cell reference)

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[SkipVought]

FYI...

Why do Dates and Times seem to be so much trouble? faq68-5827

Skip,

_[red][/red]

 

[nirod]

Thank you, your help is greatly appreciated

 

[AnotherHiggins]

Glad you got it sorted out.


[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[KenWright]

You can also just select the data and use Data / Text To Columns on it, then just specify DATE and choose the YMD option. No extra formulas and data sorted in situ.

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------