I have imported employee data from an AS400. The text for dates import as
"1012008" for 01/01/08 or 12012008 for 12/01/08. Is it possible to write a
formula to insert the "/" for 4 spaces from the left and again at 6 spaces
from the left.

I need to be able to make these usable dates without speinding a lot of
time. Other suggestions for converting these to a workable date would be
appreciated.

Thank you for your help.

Data/ Text to Colums/ Delimited/ Next/ Next/ Date : MDY
--
Gary''s Student - gsnu200829


"Still learning@work" wrote:

I have imported employee data from an AS400. The text for dates import as
"1012008" for 01/01/08 or 12012008 for 12/01/08. Is it possible to write a
formula to insert the "/" for 4 spaces from the left and again at 6 spaces
from the left.

I need to be able to make these usable dates without speinding a lot of
time. Other suggestions for converting these to a workable date would be
appreciated.

Thank you for your help.

I have tried that but it only works if there is 8 digits in the field. It
will not convert if there are 7 digits (7012008). Suggestions for correcting
that?

"Gary''s Student" wrote:

Data/ Text to Colums/ Delimited/ Next/ Next/ Date : MDY
--
Gary''s Student - gsnu200829


"Still learning@work" wrote:

I have imported employee data from an AS400. The text for dates import as
"1012008" for 01/01/08 or 12012008 for 12/01/08. Is it possible to write a
formula to insert the "/" for 4 spaces from the left and again at 6 spaces
from the left.

I need to be able to make these usable dates without speinding a lot of
time. Other suggestions for converting these to a workable date would be
appreciated.

Thank you for your help.

With a number in A1:
=IF(LEN(A1)=7,DATE(RIGHT(A1,4),LEFT(A1,1),MID(A1,2 ,2)),DATE(RIGHT(A1,4),LEFT(A1,2),MID(A1,3,2)))

for example:

12252008 12/25/2008
7252008 7/25/2008

--
Gary''s Student - gsnu200829


"Still learning@work" wrote:

I have tried that but it only works if there is 8 digits in the field. It
will not convert if there are 7 digits (7012008). Suggestions for correcting
that?

"Gary''s Student" wrote:

Data/ Text to Colums/ Delimited/ Next/ Next/ Date : MDY
--
Gary''s Student - gsnu200829


"Still learning@work" wrote:

I have imported employee data from an AS400. The text for dates import as
"1012008" for 01/01/08 or 12012008 for 12/01/08. Is it possible to write a
formula to insert the "/" for 4 spaces from the left and again at 6 spaces
from the left.

I need to be able to make these usable dates without speinding a lot of
time. Other suggestions for converting these to a workable date would be
appreciated.

Thank you for your help.

Hi,

Suppose your date are in the range A1:A1000

1. In B1 enter the formula
=RIGHT("0"&A1,8)
2. Copy it down
3. Select all the formula and choose Copy, Edit, Paste Special, Values,
4. Now run the Text to Columns wizard.
--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Still learning@work" wrote:

I have imported employee data from an AS400. The text for dates import as
"1012008" for 01/01/08 or 12012008 for 12/01/08. Is it possible to write a
formula to insert the "/" for 4 spaces from the left and again at 6 spaces
from the left.

I need to be able to make these usable dates without speinding a lot of
time. Other suggestions for converting these to a workable date would be
appreciated.

Thank you for your help.

With a number in A1:
=IF(LEN(A1)=7,DATE(RIGHT(A1,4),LEFT(A1,1),MID(A1, 2,2)),DATE(RIGHT(A1,4),LEFT(A1,2),MID(A1,3,2)))

This is a more compact way to do the same thing...

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

--
Rick (MVP - Excel)

If a macro solution would be acceptable, this one will convert, in place,
all 7 or 8 digit number within the selection into dates. Simply select all
the cells you want to convert and run this macro...

Sub MakeIntoDates()
Dim C As Range
For Each C In Selection
If C.Value Like "*#######" Then
C.Value = CDate(Format(C.Value, "&&/&&/&&&&"))
End If
Next
End Sub

--
Rick (MVP - Excel)


"Still learning@work" wrote in
message ...
I have imported employee data from an AS400. The text for dates import as
"1012008" for 01/01/08 or 12012008 for 12/01/08. Is it possible to write
a
formula to insert the "/" for 4 spaces from the left and again at 6 spaces
from the left.

I need to be able to make these usable dates without speinding a lot of
time. Other suggestions for converting these to a workable date would be
appreciated.

Thank you for your help.