CODEWITHSUNDEEP
excelvba
barooon gara
barooon gara

Reputation: 123

How to split date and time

I would like to know how to split date and time into separate rows.

enter image description here

I would like Date in Column A and time in Column B with AM/PM included.

I tried with a delimiter of space but I get errors. On the internet people were first selecting cells but I want to do it without selecting cells.

Sub CompareTime()

Dim ws As Worksheet

Dim lastRow As Long
Dim arr As Long
Dim test As Double

Set ws = ActiveSheet

Cells(1, 2).EntireColumn.Insert

'Find last data point
With ws
    lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With    

For Count = 1 To lastRow
    'split date
    Cells(1, Count).Offset(0, 1) = Split(Cells(1, Count).Value, " ")
Next Count    

End Sub

Upvotes: 1

Views: 1613

Answers (3)

barooon gara
barooon gara

Reputation: 123

So I found a super easy way after messing with the split function. I just used a delimiter of space and split the date from time with AM/PM included.

Sub CompareTime()

Dim ws As Worksheet
Dim count As Long
Dim lastRow As Long
Dim arr As Long
Dim store As Double

Set ws = ActiveSheet

Cells(1, 2).EntireColumn.Insert

'Find last data point
With ws
lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

For Count = 5 To lastRow

'split date
Cells(Count, 1).Offset(0, 1).Value = Split(Cells(Count, 1), " ")(1) & " " & Split(Cells(Count, 1), " ")(2)
Cells(Count, 1).Value = Split(Cells(Count, 1), " ")


Next Count

End Sub

Upvotes: 1

Dy.Lee
Dy.Lee

Reputation: 7567

The other method. Scott's code more simple.

Sub CompareTime()

Dim ws As Worksheet

Dim lastRow As Long
Dim arr As Long
Dim test As Double
Dim vDB, vR(), n As Long, i As Long

Set ws = ActiveSheet



'Find last data point
With ws
    .Cells(1, 2).EntireColumn.Insert
    lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    vDB = .Range("a5", "a" & lastRow)

    n = UBound(vDB, 1)
    ReDim vR(1 To n, 1 To 2)

    For i = 1 To n
        vR(i, 1) = DateValue(Format(vDB(i, 1), "yyyy/mm/dd"))
        vR(i, 2) = TimeValue(Format(vDB(i, 1), "hh:mm"))
    Next i
    .Range("a5").Resize(n, 2) = vR
    .Columns(1).NumberFormatLocal = "mm/dd/yyyy"
    .Columns(2).NumberFormatLocal = "hh:mm"
End With
End Sub

Upvotes: 2

Scott Craner
Scott Craner

Reputation: 152605

Date/time is a number and not a text string so to get the date you need to plit the integer from the decimal, then format them:

Sub CompareTime()

Dim ws As Worksheet

Dim lastRow As Long
Dim Count As Long
Dim test As Double

Set ws = ActiveSheet


'Find last data point
With ws
    .Columns(2).Insert
    lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

    For Count = 5 To lastRow
        'split date
        test = .Cells(Count, 1).Value2
        .Cells(Count, 1).Value2 = Int(test)
        .Cells(Count, 1).NumberFormat = "m/d/yyyy"
        .Cells(Count, 1).Offset(0, 1).Value2 = test - Int(test)
        .Cells(Count, 1).Offset(0, 1).NumberFormat = "hh:mm AM/PM"

    Next Count
End With

Upvotes: 4

Related Questions