When you want to record a time unit, you may normally follow the military time format. It’s a 24-hour time format that without am/pm. To Convert military time in Google Sheets you can either use formulas or the formatting option.
The text functions like TEXT, LEFT, RIGHT, and MID are vital in time formatting rather than the TIME functions. In this tutorial, I am going to use the MID, TEXT, and TIMEVALUE functions.
A 24-hour clock is normally known as military time. You can convert this Military time to 12-hour clock in Google Sheets using formulas.
In a 24-hour clock, the time is written in HH:MM or HH:MM:SS format. For example 23:15 which is equal to 11:15 PM in a 12-hour format.
Sometimes the military time may also be entered like 231500 which is equal to 23:15:00.
Let me show you how to convert military time in Google Sheets using formulas.
Formulas to Convert Military Time in Google Sheets
Here I have two types of formulas and formats. The first one is a 24-hour clock. Let’ me show you how to convert a 24-hour clock time to 12-hour clock (AM/PM) time in Google Sheets.
Convert a 24-Hour Clock Time to Standard 12-Hour Clock Time
Time in Cell A1 that in 24-hour format:
23:22:12
The Formula in Cell B1 That Converts Military Time in Google Sheets.
=text(B1,"HH:MM:SS AM/PM")
Result:Â 11:22:12 pm
If you want to convert a military time within the cell that it contains, you must format that cell without using formula.
See how to format a cell that contains military time in Google Sheets.
Steps:
Go to the Menu Format > Number > More Formats > Custom Number Format.
Apply the below format in the provided field.
hh:mm:ss AM/PM
Convert Military Time in Google Sheets
If you have a Google Spreadsheet which has the military time entered as below, you may need to use a combination formula to convert it.
Here is that second formula option. Let me show you how to convert a pure military time in Google Sheets using text and time functions.
Actually, in Cell A1, A2, and A3 contains military time. The difference of these military times with the standard 24-hour clock time is the absence of the Colon separator between the hour, minute and seconds.
This punctuation mark is necessary for Google Sheets to identify a time. Here is my formula in Cell C1 that converts military time in Google Sheets.
=text(timevalue(mid(A1,1,2)&":"&mid(A1,3,2)&":"&mid(A1,5,2)),"HH:MM:SS AM/PM")
I have copied this formula to cell C2 and C3.
What does this military time conversion formula do?
The MID formula extracts two characters each and joins the colons in between. So the military time can now be an identifiable 24-hour time format.
The next step is to use the Text function to format it to a 12-hour clock.
That’s all. Follow the above steps to convert military time in Google Sheets.
Additional Resources:
1. COUNTIFS in a Time Range in Google Sheets [Date and Time Column]
