Converting Dates after Import

[Still learning@work]

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.

 

[Gary''s Student]

Data/ Text to Colums/ Delimited/ Next/ Next/ Date : MDY

 

[Still learning@work]

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]

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

 

[Shane Devenshire]

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.

 

[Rick Rothstein]

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

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