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

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

--
Gary's Student
gsnu200704


"mcamp" wrote:

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

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


"Gary''s Student" wrote:

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

--
Gary's Student
gsnu200704


"mcamp" wrote:

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

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/ex...042111033.aspx
--
David Biddulph

"mcamp" wrote in message
...
That worked like a charm! I don't understand a word of it but thanks a
ton!

"Gary''s Student" wrote:

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

"mcamp" wrote:

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

On Wed, 7 Feb 2007 08:19:01 -0800, mcamp
wrote:

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

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

"Ron Rosenfeld" wrote in message
...

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

Format the result as you wish:

Format/Cells/Number/Date and select

On Wed, 7 Feb 2007 08:19:01 -0800, mcamp
wrote:

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.

On Thu, 8 Feb 2007 13:46:25 -0000, "David Biddulph"
wrote:

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

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.
--
David Biddulph

"Ron Rosenfeld" wrote in message
...

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

On Thu, 8 Feb 2007 13:46:25 -0000, "David Biddulph"

wrote:

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

On Thu, 8 Feb 2007 18:00:27 -0000, "David Biddulph"
wrote:

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