changing text to date format

[Guest]

I have an excel file that contains text cells which need to be formatted to
dates some are single digit months and some are double digit (3042006
11051999 1012001) etc. I need to figure out how to convert these dates into
a standard date format. I am importing them into my computer system which is
seeing them as julian dates the way they are being imported now. I need them
to be mm/dd/yy or mm/dd/yyyy format.

 

[Guest]

=DATE(RIGHT(A1,4),LEFT(A1,LEN(A1)-6),LEFT(RIGHT(A1,6),2))

 

[Guest]

That worked like a charm! I don't understand a word of it but thanks a ton!

 

[David Biddulph]

If you don't understand, then I suggest that you look up those functions in
Excel's built-in help, or on the web at
http://office.microsoft.com/en-us/excel/HP052042111033.aspx

 

[Ron Rosenfeld]

I have an excel file that contains text cells which need to be formatted to
dates some are single digit months and some are double digit (3042006
11051999 1012001) etc. I need to figure out how to convert these dates into
a standard date format. I am importing them into my computer system which is
seeing them as julian dates the way they are being imported now. I need them
to be mm/dd/yy or mm/dd/yyyy format.

=--TEXT(A1,"00\/00\/0000")

Format the result as you wish:

Format/Cells/Number/Date and select


--ron

 

[David Biddulph]

Doesn't that screw up with single digit months, Ron? Try it on the OP's
first example, 3042006, or his 3rd example, 1012001.

 

[Ron Rosenfeld]

Doesn't that screw up with single digit months, Ron? Try it on the OP's
first example, 3042006, or his 3rd example, 1012001.

Probably because I'm in the US and not UK, David, I took it that the OP's data
format was mddyyyy or mmddyyyy.

You need to look at his SECOND example: 11051999


--ron

 

[David Biddulph]

Yes, it was unfortunate that all the examples from the OP could be
interpreted either way. If his second example had been 11251999 or
25111999, then we'd all have known what language we were speaking.

I suppose that the fact he said he wanted his output formatted as mm/dd/yy
or mm/dd/yyyy format should have been a hint to me if I'd been wider awake.
It's an interesting reminder that these things need to be clearly specified,
and often need the Windows Regional Options set accordingly. As it was,
your formula and the one from Gary's Student gave totally different answers
on my machine.

 

[Ron Rosenfeld]

Yes, it was unfortunate that all the examples from the OP could be
interpreted either way. If his second example had been 11251999 or
25111999, then we'd all have known what language we were speaking.

I suppose that the fact he said he wanted his output formatted as mm/dd/yy
or mm/dd/yyyy format should have been a hint to me if I'd been wider awake.
It's an interesting reminder that these things need to be clearly specified,
and often need the Windows Regional Options set accordingly. As it was,
your formula and the one from Gary's Student gave totally different answers
on my machine.

Yes, Gary's formula will give proper results independent of the Windows
Regional Settings. Mine will only give the "correct" answer with US style
Regional Settings.
--ron