Search code examples
excelvbadatetimedata-conversion

VBA Turn Column with Date and Time to 24hrs (Excel)

I have a date & time column with many rows that look like this:

11/11/19 3:54PM

11/11/19 5:33AM

I would like to basically leave the dates alone but just change the times in place to 24 hour and deleting the am or pm and add G at the end. Like this:

11/11/19 15:54G

11/11/19 05:33G

Is there a simple way to do this or do i have to split date and time over 2 columns, do the conversion then concatenate.

Thanks.

I tried to do this thru cell formatting


Sub Converter()

Dim Focus As String

With ActiveSheet
Range("A3:A100").Select

Do
Focus = Format$(ActiveCell, "dd/mm/yyyy hh:mm")

ActiveCell.Value = Focus
ActiveCell.Offset(1, 0).Select

Loop Until IsEmpty(ActiveCell.Offset(0, 0))

End With


End Sub

I would like to achieve this in vb

Solution

  • Close: Format(Now(), "dd/mm/yyyy h:mm") & "G"

    You could use h:mmG in the format, but that is less clear for others looking in the future.