30 Advanced Excel Tips for 2026

Excel Champs / Excel Formulas List / How to Change Time Format in Excel
By Puneet Gogia (Microsoft MVP)

How to Change Time Format in Excel

30+ Advanced Excel Tips for 2026

No spam. Only Excel Stuff. God Promise. 😊

To change how a time displays, select the cells, press Ctrl+1, choose Time, and pick a format — the underlying value doesn’t change, so it still works in calculations. For a formatted copy in another cell, use =TEXT(A1,"hh:mm AM/PM"), but that returns text and can’t be used in further time maths.

For elapsed time over 24 hours, use a custom format of [h]:mm — without the square brackets, 27 hours displays as 3:00.

Excel stores time as a decimal number, where a full day is 1. So, one hour is 1/24 (0.0417), and 0.75 is 6:00 PM. There are two different ways to format a decimal value as a time.

  • Apply Time Format on the same cell.
  • Create a new time value in a different cell.
Change Time Format / Excel Formula

Change Time Format using Custom Formatting

In the following example, you have a decimal value in cell A1, and now you need to apply the time format to this cell.

change-time-format

You can use the following steps to change the time format.

time-category-under-format-cells
  1. First, use the keyboard shortcut Ctrl + 1 to open the Format Cells dialog box.
  2. After that, click on the Time category from the list.
  3. Now, at the right side of the dialog box, select the time format you want to apply.
  4. In the end, click OK to apply the format.
updated-time-format

You can use any of the formats in the list in the Format Cells dialog box.

list-of-time-formats

And if the format you want isn’t in the list, click the Custom category and type your own format code in the Type box. Here are the codes you will use most:

  • h:mm: hours and minutes in the 24-hour clock, like 18:00.
  • h:mm AM/PM: the 12-hour clock, like 6:00 PM.
  • hh:mm:ss: hours, minutes and seconds with leading zeros, like 06:05:30.
  • [h]:mm: total hours above 24, like 30:00 for 1.25 days. Without the square brackets, Excel starts again from 0 after 24 hours and shows 6:00.

Use TEXT Function to Change the Time Format

In Excel, there’s a TEXT function that you can use to change the format of time to get it in a new cell with a new format. In the following example, you have a decimal value in cell A1. To change the time format, you need to use the TEXT function. You can use the following steps:

text-function-for-time-format-change
  1. First, in cell B1, enter the TEXT function (=TEXT).
  2. After that, refer to cell A1.
  3. Next, in the second argument, specify the time format you want to use. Here, we are using (hh:mm AM/PM).
  4. In the end, enter the closing parenthesis and hit Enter to get the result.
=TEXT(A1,"hh:mm AM/PM")

Just remember that TEXT returns text, not a time value. It looks right, but you can’t use the result in calculations like adding or subtracting times. If you need to calculate with it, use the format from Format Cells instead.

Convert Decimal Hours to Time Format

Sometimes the number in the cell is in hours, like 1.5 for one and a half hours, not a fraction of a day. If you apply a time format directly, 1.5 shows as 12:00 (one and a half days). First, divide the number by 24 to turn hours into a day fraction, and then apply the h:mm format:

=A1/24

Or, to do both in one step and get the result as text:

=TEXT(A1/24,"h:mm")

With 1.5 in A1, both give 1:30. In the same way, divide by 1440 for minutes and by 86400 for seconds.

Frequently Asked Questions

Why does my time show as #####?

Either the column is too narrow, so widen it, or the time is negative, like a start time minus a later end time. Excel can’t show negative times in the default date system.

How do I change from 12-hour to 24-hour time?

Use a format without AM/PM, like h:mm or hh:mm. As soon as the format has AM/PM in it, Excel uses the 12-hour clock.

Why doesn’t the format change the time in my cell?

The time is probably stored as text, often from imported data. Convert it to a real time with the TIMEVALUE function, like TIMEVALUE(A1), or with Data ➜ Text to Columns ➜ Finish, and then apply the format.

Puneet Gogia

Written by

Puneet Gogia

Microsoft MVP — Excel · Founder, Excel Champs

Twelve years of teaching Excel, and a decade before that using it as a data analyst. Every tutorial here is written from a file I actually built.

3M+ readers helped
1,000+ tutorials written
Since 2015 teaching Excel

Leave a Comment