Time management is a must-have skill in our fast-paced world. Whether you’re completing school assignments, scheduling a meeting, or planning a trip, converting time formats is crucial.

A common challenge is switching between the 24-hour clock (military time) and the standard 12-hour clock.

The concept of a 24-hour system involves dividing a day into a continuous period of 24 hours, commencing at 00:00 hours (midnight) and concluding at 23:59 hours. In contrast, the 12-hour system partitions this time into two distinct cycles, each lasting 12 hours.

The first cycle spans from 00:00 am hours (midnight) to 12:00 pm (noon), while the second cycle extends from 12:00 pm (noon) to 00:00 am hours (midnight) again. This division allows for a more conventional distinction between the two halves of the day.

Time data can be presented in either format. This can require that you convert it to a format that feels more intuitive for comprehension.

Fortunately, switching time values between 24-hour and 12-hour time systems in Google Sheets is incredibly simple.

In this article, you will learn step-by-step instructions on how to seamlessly convert time formats from the 24-hour clock to the 12-hour clock using Google Sheets functions and features.

Convert Between 24 and 12 Hours with the TEXT Function

The TEXT function in Google Sheets is a powerful tool that allows you to format and manipulate data in custom ways.

With the TEXT function, you can transform numeric values into various text formats. This includes dates, times, currencies, percentages, and more.

=TEXT(number, format)

The TEXT function uses two parameters:

number – the value you want to format.

format – a string of formatting instructions that specify how you want to format the number. The format string uses special characters that control how the number appears.

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

To use the TEXT function to convert 24-hour to 12-hour time format, you can use this syntax. The syntax takes the value in the referenced cell – B4 – and converts the time to a 12-hour format using the format string HH: MM AM/PM.

=TEXT(B4, “HH:MM”)

Converting from a 12-hour to a 24-hour time system uses the same syntax but without the AM/PM bit at the end.

With the TEXT function syntax, you can effortlessly convert time values between the 12-hour and 24-hour time systems.

You should know that the TEXT function converts numbers to text. This isn’t ideal when working with time. But worry not, as the next two methods retain time values as numbers to ensure a seamless experience.

Convert Between 24 and 12 Hours with the QUERY Function

Google Sheet’s QUERY function is a versatile tool for extracting and manipulating data from a given range or table.

It works similarly to SQL SELECT and enables filtering, sorting, summarizing, and formatting data. This makes it a useful tool for almost any data-driven task in the spreadsheet.

=QUERY(data, query,  [headers])

The QUERY function has the following parameters:

data – the range of cells containing the values you want to query.

query – the computation you want to carry out on the data.

[headers] – an optional parameter that allows you to specify the number of rows used as column headers in the data. When left out or set to -1, it guesses the number of rows used as column headers from the data.

=QUERY(B3:B13, "FORMAT B 'HH:MM AM/PM'", 1)

Use the syntax above to change the time format from 24- to 12-hour clock.

In the syntax, the QUERY function uses the FORMAT query to format the values in the referenced range – B3:B13. The syntax uses the same formatting instructions passed into the TEXT function to format the time values.

The difference between the QUERY and TEXT output is that the QUERY function retains the numeric format of the converted values.

When using the QUERY function, you should make sure there are enough empty cells to accommodate the output because it’s an array formula.

=QUERY(B3:B13, "FORMAT B 'HH:MM'",1)

To change time from a 12-hour to a 24-hour system using QUERY, simply remove the AM/PM bit from the syntax.

Convert Between 24 and 12 Hours with Custom Date and Time Format

Although Google Sheets does offer several formatting presets, it also provides the option to create custom formats. One such option is the Custom date and time format, which enables you to modify and personalize your spreadsheets.

Converting time between 12- and 24-hour clock using the Custom date and time feature is a straightforward process that involves only a few steps.

1. Select the cells containing the time values you want to convert

2. While keeping the cells selected, go to the Format menu and click on Number. Select the Custom date and time option from the contextual menu.

3. Clear the contents in the Custom date and time formats text box using the Backspace or Delete button. Afterwards, click the dropdown arrow and select Hour.

This will add the Hour component to the text box.

To show leading zeros in the hour component of your time format, click on the Hour section and select Hour with leading zero (01). This option displays a leading zero in front of single-digit hour values.

Once you’ve made your selection, add a colon (:) to separate the hour from minutes components.

Repeat this process until you have all components for changing the time formats filled in the text box. Click on the Apply button when you’re done.

Now, the time in the selected cells will be formatted in a 24-hour time system.

To convert from a 12 to 24-hour time system, remove the AM/PM component from the formatting instruction.

Convert Between 24 and 12 Hours with Apps Script

Apps Script in Google Sheets is a scripting language that enables you to extend and customize your spreadsheet’s functionality.

With Apps Script, you can create a feature that will automate the tasks of converting time between a 12- and 24-hour clock.

Before creating an Apps script, you need to open the editor window. To do this, go to the Extensions menu and click on Apps Script.

function twelveHourTime () {
  var ss = SpreadsheetApp.getActive()
  var range = ss.getActiveRange()

  range.setNumberFormat("HH:MM AM/PM")
  
}

function twentyFourHourTime () {
  var sss = SpreadsheetApp.getActive()
  var cells = sss.getActiveRange()

  cells.setNumberFormat("HH:MM")

}

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Format Time')
  .addItem('12-hour Time', 'twelveHourTime')
  .addSeparator()
  .addItem('24-hour Time', 'twentyFourHourTime')
  .addToUi();
}

After opening the editor, paste the syntax into the editor window. Click on the Save and Run buttons to grant the necessary permissions. Then refresh your spreadsheet.

The syntax comprises three functions:

• twelveHourTime() – this function is responsible for formatting the selected range of cells to display the time in a 12-hour format with AM/PM indicators. It uses the setNumberFormat() method to apply the HH:MM AM/PM format to the selected range.
• twentyFourHourTime() – this function also uses the setNumberFormat() method with the format code HH:MM to format the selected range in 24-hour format.
• onOpen() – this is a special trigger function in Apps Script that executes automatically when you open a Google Sheet. It uses the createMenu() method to create a custom menu named Format Time.

The menu has two items – 12-hour Time and 24-hour Time. The addItem() method adds each item to the menu, associating it with its respective function – twelveHourTime and twentyFourHourTime.

By using the provided syntax, you can create a custom menu named Format Time. This menu offers convenient options that allow you to effortlessly switch the time format between a 12-hour and 24-hour clock with a single click.

This intuitive menu eliminates the need to write lengthy function syntaxes and click multiple times. With this, you can save valuable time and transforming the time display becomes a seamless and efficient task.

Conclusions

Google Sheets provides you with a versatile toolkit to easily manage time-related data. With the right function and just a few clicks, you can switch between 12-hour and 24-hour formats.

The TEXT function and QUERY function provide powerful yet simple solutions to smoothly convert time values between 12-hour and 24-hour formats. And with the Custom date and time feature, you can further customize the time to meet your specific needs.

For even greater efficiency, use Apps Script to create a custom menu for this task. With these tools at your disposal, wrangling time data in Google Sheets has never been smoother or more efficient.

Do you find this helpful? Let us know in the comments!