How to Convert Military Time in Google Sheets

Are you tired of grappling with military time when working with Google Sheets? Don’t worry, we’ve got you covered! In this article, we’ll show you two simple methods to convert military time to a 12-hour clock format. Whether you prefer using formulas or formatting options, we’ve got the solution for you.

Formulas to Convert Military Time in Google Sheets

Convert a 24-Hour Clock Time to Standard 12-Hour Clock Time

Let’s start with the first method, which involves converting a 24-hour clock time to the standard 12-hour clock format. If you have a military time in cell A1, you can use the following formula in cell B1 to achieve the conversion:

=text(B1, "HH:MM:SS AM/PM")

For example, if the military time in cell A1 is 23:22:12, the resulting converted time in cell B1 will be 11:22:12 pm.

Alternatively, if you want to convert a military time within a cell itself (without using a formula), you can simply format the cell as follows:

1. Go to the Menu Format > Number > More Formats > Custom Number Format.
2. Apply the format “hh:mm:ss AM/PM” in the provided field.

Convert Military Time in Google Sheets

If you have a Google Spreadsheet with military time entered without colons (e.g., 231500), you’ll need to employ a combination formula to convert it. Here’s how you can do it:

1. Assume that cells A1, A2, and A3 contain military time without colons.
2. Use the following formula in cell C1 to convert the military time:

=text(timevalue(mid(A1, 1, 2)&":"&mid(A1, 3, 2)&":"&mid(A1, 5, 2)), "HH:MM:SS AM/PM")

You can then copy this formula to cells C2 and C3 to convert the military time in those cells as well.

To understand how this formula works, consider the MID function, which extracts two characters each from the military time and joins them with colons. This step enables Google Sheets to recognize the time as a 24-hour format. Next, the Text function is used to format the time as a 12-hour clock.

That’s it! You now have two methods to effortlessly convert military time in Google Sheets using either formulas or formatting options. With these techniques, you can easily work with different time formats in your spreadsheets.

For more helpful resources and tutorials related to Google Sheets, visit Mr Reviews.