When recording time, you may often use the military time format, which is a 24-hour format without AM/PM. In Google Sheets, you can use formulas or the formatting menu to convert military time to standard time (a 12-hour format with AM/PM).

In a 24-hour clock, time is written as HH:MM or HH:MM:SS. For example, 23:15 is equal to 11:15 PM in a 12-hour format. Sometimes, military time might also be entered as 231500, which corresponds to 23:15:00.

Let me show you how to convert military time to standard time in Google Sheets using both formulas and custom time formatting.

Convert Military Time to Standard Time Using Custom Time Formatting

To convert military time to standard time directly within a cell or cell range, you can apply custom formatting without using a formula. Follow these steps:

1. Select the cell or range that contains the time.
2. Click Format > Number > Custom Date and Time.
3. Choose a format, such as 1:25 PM or 1:25:59 PM.
4. Click Apply.

There is another option using custom number formatting:

1. Click Format > Number > Custom Number Format.
2. Enter one of the following time formats in the provided field: hh:mm AM/PM or hh:mm:ss AM/PM.
3. Click Apply.

Convert Military Time to Standard Time Using Formulas

When using a formula to convert military time to standard time, remember that the result will be in text format, which means it won’t be usable in calculations.

Assume the time in cell A1 is 23:22:12. The following formula in cell B1 will convert it to 11:22:12 pm:

=TEXT(A1,"HH:MM:SS AM/PM")

If you have military time entered as 232212 (without the standard colon delimiter separating hours, minutes, and seconds) in column A, you can convert it to standard time in a 12-hour format using the following formula:

Enter this formula in cell B1 and drag it down as needed:

=TEXT(TIMEVALUE(LEFT(A1, 2)&":"&MID(A1, 3, 2)&":"&RIGHT(A1, 2)),"HH:MM:SS AM/PM")

Explanation:

• The LEFT, MID, and RIGHT functions extract two digits from the left, middle, and right of the string, respectively.
• The extracted parts are combined using the ampersand (&), with “:” as the delimiter.
• TIMEVALUE converts the resulting text into an actual time value, and TEXT formats it into a 12-hour time format.

Alternatively, you can use the following REGEXREPLACE formula to convert military time (entered without colons) to standard 12-hour time format:

=TEXT(TIMEVALUE(REGEXREPLACE(REGEXREPLACE(A1&"", "(\d{2})", "$1:"), ":$", "")),"HH:MM:SS AM/PM")

This formula uses REGEXREPLACE to insert colons after every two digits, except for the last pair and then converts it to standard time.

Resources

• Converting Time Duration to Day, Hour, and Minute in Google Sheets
• COUNTIFS in a Time Range in Google Sheets [Date and Time Column]
• How to Add Hours, Minutes, Seconds to Time in Google Sheets
• How to Format Time to Millisecond Format in Google Sheets
• How to Increment Time by Minutes or Hours in Google Sheets
• Convert Unix Timestamp to Local DateTime and Vice Versa in Google Sheets
• How to Extract Time from DateTime in Google Sheets: 3 Methods