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

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

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