Excel Convert yyyymmdd text to Date format

Question

Excel Convert yyyymmdd text to Date format

nirod (TechnicalUser)

(OP)

26 Jul 06 11:00

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 (TechnicalUser) · Answer 1 · 2006-07-26 11:04:44

In a column to the right of the imported data, use this formula:
=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))
Copy the formula down as far as needed.

(Replace A1 with the appropriate cell reference)

_____
-John
The plural of anecdote is not data

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

SkipVought (Programmer) · Answer 2 · 2006-07-26 11:08:56

FYI...

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

Skip,

nirod (TechnicalUser) · Answer 3 · 2006-07-26 12:18:54

RE: Excel Convert yyyymmdd text to Date format

nirod (TechnicalUser)

(OP)

26 Jul 06 12:18

Thank you, your help is greatly appreciated :)

AnotherHiggins (TechnicalUser) · Answer 4 · 2006-07-26 12:19:42

Glad you got it sorted out.
cheers

_____
-John
The plural of anecdote is not data

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

KenWright (TechnicalUser) · Answer 5 · 2006-07-27 01:38:23

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
----------------------------------------------------------------------------

Come Join Us!

Posting Guidelines

Jobs

Excel Convert yyyymmdd text to Date format