Aargh! Can anyone help me custom format this numeral in Excel?

[rexnervous]

Should be easy, but I can't figure it out.

I have one column of data that looks like this:

20030125115000

This is non-parsed date/time data; in other words, this numeral marks 11:50:00 AM on January 25, 2003. (hours are military time, so there's no 'marker' for AM v. PM).

I've tried various ways of customizing format, but I guess I don't understand how the tool works.

Anyone know?

I don't care how the output looks per se, just want it readable.

[rexnervous]

I should add that I don't care about the time, so if there's a way to just 'delete' the last six digits and only display date that'd be even better.

[starfish]

goto format, cell, type in:
yyyymdhms

If you don't want the time, leave off hms

[rexnervous]

thanks, but that didn't do it. I've tried all kinds of variations of that, but it's not doing anything.

No matter what I do, all I get is a seemingly unending string of "#" (###################...)

[greyseal]

No matter what I do, all I get is a seemingly unending string of "#" (###################...)

Make sure that the column is large enough to fit the formatted data in.

[Dewey Finn]

Assuming that the data is in cell A1, try this in cell A2:

=DATE(MID(A1,1,4),MID(A1,5,2),MID(A1,7,2))

[greyseal]

Actually, after fooling around with this example a bit, it doesn't seem to work.

SMTWTFS seems to have the best workaround.

[5 time champ]

FORMAT/
CELLS/
NUMBER :

####-##-##-######

returns:

2003-01-25-115000

Appears much more "readable"

[rexnervous]

Thanks for replies. I did it SMTWTFS's way.