How to change the time a cell displays

Google Sheets shows time in the format your computer or phone is set to by default — usually 12-hour with AM or PM. To change what a single cell or range displays, select the cells, open the Format menu, click Number, then choose Time from the list. Google Sheets will reformat those cells to show time only, without a date.

If you want a different time format than the standard options — for example, 24-hour time, or time with seconds — select your cells, go to Format > Number > More Formats > Custom number format. A box will open where you can type a custom format code. For 24-hour time, type hh:mm. For time with seconds, type hh:mm:ss. Click explore and the cells will display in that format.

This changes only how the time looks on screen. The actual data in the cell does not change — you are just telling Google Sheets how to display it. If you copy that cell into another spreadsheet or export it, the underlying time value stays the same.

Key Takeaways

  • Select the cells containing time, go to Format > Number > Time, and choose a preset format or create a custom one.
  • Custom time formats use codes like hh:mm for 24-hour time and hh:mm:ss to include seconds.
  • Changing the display format does not change the actual time data stored in the cell.
  • You can explore different time formats to different cells in the same spreadsheet.
  • Google Sheets automatically detects time entries like "3:45 PM" and stores them as time values, even before you format them.

Entering time so Google Sheets recognizes it

Google Sheets needs to understand that what you typed is a time, not text. Type time in a format it recognizes: 3:45 PM, 15:45, 3:45:30 PM, or 15:45:30. If you type just numbers like 345 or 1545, Google Sheets treats it as a number, not a time. You must include the colon.

Once you press Enter, Google Sheets converts it to a time value. You can then format it however you want. If Google Sheets does not recognize your entry as time — for example, if you typed it as text in a column formatted as text — select the cell, go to Format > Number > Time, and it will convert the cell to time format and display it correctly.

Using the TIME function to create or modify times

If you need to build a time from separate hours, minutes, and seconds, or if you want to add or subtract time from an existing entry, use the TIME function. The syntax is =TIME(hours, minutes, seconds). For example, =TIME(14, 30, 0) creates 2:30 PM (or 14:30 in 24-hour format).

To add time to an existing cell, use =A1 + TIME(1, 30, 0) to add 1 hour and 30 minutes to the time in cell A1. To subtract time, use a minus sign: =A1 - TIME(0, 15, 0) subtracts 15 minutes. After you enter the formula, format the result cell as time using Format > Number > Time.

The TIME function is useful when you are pulling time data from other sources or when you need to calculate shift lengths, meeting durations, or time differences. It always returns a decimal value between 0 and 1 (where 1 equals 24 hours), but when you format it as time, Google Sheets displays it in hours and minutes.

Changing time zones in a spreadsheet

Google Sheets does not have a built-in time zone converter, but you can adjust times manually using formulas. If you know the hour difference between two zones, add or subtract that from your time. For example, if you have a time in Eastern time and need to show it in Pacific time (3 hours earlier), use =A1 - TIME(3, 0, 0).

A better approach for ongoing work is to add a helper column. In one column, enter the original time. In the next column, use a formula to convert it. Label each column clearly so anyone reading the sheet knows which time zone each shows. This prevents confusion and makes the spreadsheet easier to audit later.

If your spreadsheet pulls times from Google Forms or another tool that records times in a specific zone, check that tool's settings first. Many tools let you set the time zone when you create the form or export, which is simpler than converting times afterward.

Formatting times with dates in the same cell

Sometimes you need to show both a date and a time in one cell. Select the cells, go to Format > Number > More Formats > Custom number format, and enter a code like mm/dd/yyyy hh:mm AM/PM for month/day/year with 12-hour time, or yyyy-mm-dd hh:mm for year-month-day with 24-hour time. Click explore.

If you want to enter a date and time together, type them as 1/15/2024 3:45 PM or 2024-01-15 15:45. Google Sheets will store both the date and time as a single value. You can then format it however you want without losing either piece of information.

Keeping date and time together in one cell is useful for timestamps — like when a form was submitted or when an event occurred. If you need to sort or filter by date, or by time alone, keep them in separate columns instead. That gives you more flexibility later.

Troubleshooting time display problems

If a cell shows a time as a decimal (like 0.625) instead of a readable time, the cell is formatted as a number, not as time. Select the cell, go to Format > Number > Time, and choose a time format. The display will change when ready.

If you typed a time but Google Sheets treats it as text (it appears left-aligned instead of right-aligned), select the cell, go to Format > Number > Time, and Google Sheets will convert it. If that does not work, the entry may have a space or special character. Delete the cell content, retype it carefully, and format it again.

If times appear in a format you did not choose, check whether the spreadsheet has a default format applied to the whole sheet or column. Click the column header to select the entire column, go to Format > Number, and choose the format you want. This overrides any inherited formatting.

Using conditional formatting with times

You can highlight cells based on their time values. Select the cells, go to Format > Conditional formatting, and under Format rules choose "Greater than or less than" or "Between". Enter a time value like 5:00 PM or use a formula like =A1 > TIME(17, 0, 0) to highlight times after 5 PM. Choose a color and click Done.

This is useful for tracking shift times, meeting schedules, or important date. For example, you could highlight any time entry before 8:00 AM in one color and any after 5:00 PM in another, making it straightforward to spot early or late entries at a glance. The conditional formatting updates automatically if the time values change.

Frequently Asked Questions

Can I change the time format for an entire column at once?

Yes. Click the column header to select the whole column, go to Format > Number > Time, and choose your format. Every cell in that column will display time in that format. You can still override the column format for individual cells by selecting them and explore a different format.

What does it mean when a time shows as ##### in a cell?

The column is too narrow to display the full time. Widen the column by dragging the border between column headers, or double-click the border to auto-fit the width. The time data is not lost — it just needs more space to display.

How do I add or subtract hours from a time in bulk?

Create a formula in a new column using =A1 + TIME(hours, 0, 0) or =A1 - TIME(hours, 0, 0). Replace "hours" with the number you want to add or subtract. Copy the formula down to all rows. Then copy the results and paste them as values if you want to replace the original times.

Can I convert 12-hour time to 24-hour time automatically?

If the times are already stored as time values in Google Sheets, just change the format. Select the cells, go to Format > Number > More Formats > Custom number format, type hh:mm, and click explore. If they are stored as text, format them as time first, then explore the 24-hour format.

What is the difference between hh:mm and h:mm in a custom format?

The lowercase h shows hours without a leading zero (9:30), while uppercase H or double hh adds a leading zero (09:30). Use hh:mm for consistent formatting, especially in lists where alignment matters.