split date using VBA

[HappyLadyToo]

Hello All,

I've looked all over for this answer and though there's plenty of info, I can't quite figure this out. I have a date (6-Jan-14) that needs to be split into 3 columns. When I use text to columns, I receive this in each column:


-- removed inline image ---


While using text to columns if I change each column to text, the split looks great but I cannot turn month and day into two digits. If I convert the column to general, text or number before splitting the date, I receive Julian dates.

I am creating a large macro and this is one step of the macro.

 

[Chris Macro]

Hopefully this can get you started:

<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> SplitDate()<br><br><SPAN style="color:#007F00">'Assuming Date is in A1</SPAN><br><br>Range("B1") = Day(Range("A1"))<br>Range("C1") = Month(Range("A1"))<br>Range("D1") = Year(Range("A1"))<br><br><SPAN style="color:#007F00">'Format Cells to have Leading Zero</SPAN><br>    Range("B1:D1").NumberFormat = "00"<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[JoeMo]

Here's another alternative - a UDF you can use directly on the worksheet. It's an array function so you would use it like this:
If the date is in A1 then select B1:D1 and enter: =SplitDate(A1) then confirm with ctrl+shift+enter.

Function SplitDate(R As Range)
Dim vO As Variant
If R.Count > 1 Then Set R = R.Cells(1, 1)
If Not IsDate(R) Then
    SplitDate = CVErr(xlErrNA)
    Exit Function
End If
vO = Split(R.Value, "/")
For i = LBound(vO) To UBound(vO)
    If Len(vO(i)) = 1 Then vO(i) = "0" & vO(i)
Next i
SplitDate = vO
End Function

 

[HappyLadyToo]

Hopefully this can get you started:

Sub SplitDate()

'Assuming Date is in A1

Range("B1") = Day(Range("A1"))
Range("C1") = Month(Range("A1"))
Range("D1") = Year(Range("A1"))

'Format Cells to have Leading Zero
****Range("B1:D1").NumberFormat = "00"

End Sub

Thank you both your help. This macro works beautifully however I have one more request. How can I get this macro to split all of the dates within a column instead of just one cell?

 

[Chris Macro]

Try this:

<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> SplitDate()<br><br><SPAN style="color:#007F00">'NOTE: Run this with the cells selected that you want to split</SPAN><br><br><SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range<br><br><SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> rng <SPAN style="color:#00007F">In</SPAN> Selection.Cells<br>    rng.Offset(0, 1) = Day(rng.Value)<br>    rng.Offset(0, 2) = Month(rng.Value)<br>    rng.Offset(0, 3) = Year(rng.Value)<br><br>    <SPAN style="color:#007F00">'Format Cells to have Leading Zero</SPAN><br>        rng.Offset(0, 1).Resize(1, 3).NumberFormat = "00"<br><br><SPAN style="color:#00007F">Next</SPAN> rng<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[HappyLadyToo]

Try this:

Sub SplitDate()

'NOTE: Run this with the cells selected that you want to split

Dim rng As Range

For Each rng In Selection.Cells
****rng.Offset(0, 1) = Day(rng.Value)
****rng.Offset(0, 2) = Month(rng.Value)
****rng.Offset(0, 3) = Year(rng.Value)

****'Format Cells to have Leading Zero
********rng.Offset(0, 1).Resize(1, 3).NumberFormat = "00"

Next rng

End Sub

x

 

[HappyLadyToo]

Thanks! With a couple of changes, it worked beautifully.

 

[Chris Macro]

No problem, glad it worked out for you!