# Thread: Date Format (97 SR1)

1. ## Date Format (97 SR1)

Is there a way to change a column that has the date entered like this: 20000802, into the date like so: 8/2/2000? Can I do it with a macro or something? When I enter hyphens after the 4th and 6th numbers it will change to the date format. How could I program it to enter these hyphens? I'm a beginner so I would need detailed instructions. Thanks.

2. ## Re: Date Format (97 SR1)

kanders1

Happy New year

Yes you can do it via a macro, or via a formula, faster.

I am sory my Excel is calculating a huge model at the moment, but look at:

1) Concatenate function
2) Mid Function.

You can do something like Mid(A1,1,4) and this will get you the year them Mid(A1,5,1) will get you the Month, and then Mid(A1,6,2) to get the Day. Then

Use the concatenate function to assemble them back with / in between.

HTH

Wassim

3. ## Re: Date Format (97 SR1)

If the date is always in the quoted 8 digit format, this formula (assuming the source cell is A1) will convert it:

=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))

If you make these conversions frequently a macro would be better.

4. ## Re: Date Format (97 SR1)

Here we go with macro. With Excel open, Alt-F11 opens the Visual Basic Editor. On the left side, the Project Explorer, locate your Personal .xls, create a Module if there isn't one, and copy this text into the code window on the right hand side, save Personal.xls:

Sub T8Date2DateVal()
Application.ScreenUpdating = False
Dim intCalcSetting As Integer
Dim rngCell As Range
Dim strCVal As String
If vbYes = MsgBox("Are you certain you want to convert these dates" & _
vbLf & "from Text yyyymmdd to Date mm/dd/yyyy?", vbYesNoCancel) Then
intCalcSetting = Application.Calculation
Application.Calculation = xlCalculationManual
For Each rngCell In Selection.SpecialCells(xlCellTypeConstants)
strCVal = rngCell.Value
If Len(strCVal) = 8 And Not InStr(strCVal, "/") Then
rngCell.Value = Left(strCVal, 4) & "/" & Mid(strCVal, 5, 2) & _
"/" & Right(strCVal, 2)
rngCell.NumberFormat = "mm/dd/yyyy"
End If
Next rngCell
Application.Calculation = intCalcSetting
End If
Application.ScreenUpdating = True
End Sub

5. ## Re: Date Format (97 SR1)

Thank you very much! The macro worked perfectly.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•