How to Format Time in VBA (With Examples)

You can use the Format function in VBA with the following arguments to format times in Excel in a specific manner:

h: hours without zeros
hh: hours with zeros
n: minutes without zeros
nn: minutes with zeros
s: seconds without zeros
ss: seconds with zeros
AM/PM: Display AM/PM

The following example shows how to format times using VBA in practice.

Example: Format Times Using VBA

Suppose we have the following column of datetimes in Excel:

We can create the following macro to format the times in column A in specific ways and output the formatted times in columns B through E:

Sub FormatTime()

Dim i As Integer

For i = 2 To 8
  Range("B" & i) = Format(Range("A" & i), "h")
  Range("C" & i) = Format(Range("A" & i), "h:nn")
  Range("D" & i) = Format(Range("A" & i), "h:nn:ss")
  Range("E" & i) = Format(Range("A" & i), "h:nn:ss AM/PM")
Next i

End Sub

When we run this macro, we receive the following output:

Columns B through E now show each time in column A formatted in a specific way.

Note that you can also use the shortcut formats of Short Time, Medium Time and Long Time:

Sub FormatTime()

Dim i As Integer

For i = 2 To 8
  Range("B" & i) = Format(Range("A" & i), "Short Time")
  Range("C" & i) = Format(Range("A" & i), "Medium Time")
  Range("D" & i) = Format(Range("A" & i), "Long Time")
Next i

End Sub