CODEWITHSUNDEEP
excel
Access Newbie
Access Newbie

Reputation: 19

How to convert Date Time to just Time?

I have a column of date time. I have to remove the date part. I simple want to run a macro that will do that. WHen I record macro, do the delete and then stop, then run it on the next row, it gives the value below. How does one globalize so I can run on all rows this task?

 2017-06-26 14:41:00

the macro is this:

Sub Macro9()
'
' Macro9 Macro
'

'
    ActiveCell.FormulaR1C1 = "2:41:00 PM"
    ActiveCell.Offset(1, 0).Range("A1").Select
End Sub

Upvotes: 1

Views: 145

Answers (1)

Jarom
Jarom

Reputation: 1077

Here is a simple macro to accomplish what you are looking to do. I assumed that you wanted to convert from military time to AM/PM. You will have to adjust the locations of cells to fit your spreadsheet. This is just going through all of the values in column A and turning them into just AM/PM time and spitting them out in coulmn B. Instead of looping through all of the rows you could also define your own single input function with the same logic.

Sub test()

Dim dt As String
Dim tm As String
Dim hr As String
Dim row_ct As Integer

row_ct = Range("A1").End(xlDown).Row


For i = 1 To row_ct
    dt = Cells(i, 1)
    tm = Right(Cells(i, 1), 8)
    hr = Left(tm, 2)

    If hr < 12 Then
        tm = tm & " AM"
    ElseIf hr = 12 Then tm = tm & " PM"
    ElseIf hr > 12 and hr - 12 < 10 then tm = 0 & (Left(tm, 2) - 12) & Right(tm, 6) & " PM"
    Else: tm = left(tm, 2) - 12 & right(tm, 6) & " PM"
    End If

    Cells(i, 2) = tm
Next i


End Sub

Here is how you can make a custom function that handles this:

Function tm(date_time)

If Left(Right(date_time, 8), 2) < 12 Then
    tm = Right(date_time, 8) & " AM"
ElseIf Left(Right(date_time, 8), 2) = 12 Then tm = Right(date_time, 8) & " PM"
ElseIf Left(Right(date_time, 8), 2) > 12 Then tm = Left(Right(date_time, 8), 2)- 12 & Right(date_time, 6) & " PM"`
End If

End Function

Depending on the application, one will probably work better than the other.

Upvotes: 1

Related Questions