Format Hours and Minutes in Excel for Clear Results

To format hours and minutes in Excel, select the cells, open Format Cells with Ctrl+1 on Windows, choose Custom, and enter h:mm for a time of day or [h]:mm for elapsed hours. Use the bracketed format for totals that may reach 24 hours or more; it keeps the full total visible instead of starting over at zero.

What You’ll Need

An Excel workbook containing times, durations, or a blank range where you plan to enter them.
A clear idea whether the values represent clock times, such as 9:30 AM, or elapsed amounts, such as 9 hours and 30 minutes. The right format depends on this distinction.

The steps below are for Microsoft Excel for Microsoft 365 on Windows. The formatting codes also work in current Excel for Mac and Excel for the web, though the dialog and keyboard shortcuts can differ slightly.

Step-by-Step

Decide what the values mean. A clock time identifies a point in the day: 7:45 AM, for example. An elapsed duration measures an amount of time: a shift lasting 7 hours and 45 minutes. For clock times, use a format such as h:mm AM/PM or hh:mm. For durations, use [h]:mm, especially for totals. This choice matters because Excel stores times as fractions of a 24-hour day, and an unbracketed hour display can wrap after 24 hours.
Select the cells to format. Select one cell, a range, or an entire column if you want the same format applied to future entries in that column. To format nonadjacent ranges, hold Ctrl while selecting them on Windows. Formatting changes how Excel displays numeric values; it does not alter the underlying value or turn text into a time.
Open the number-format controls. Press Ctrl+1 on Windows to open the Format Cells dialog. You can also go to Home > Number Format dropdown > More Number Formats. In Excel for Mac, Command+1 opens Format Cells in current versions. In Excel for the web, use Home > Number Format and choose More Number Formats if available; the dialog may be more limited than the desktop version.
Choose a built-in time format or create a custom one. In Format Cells, select the Number tab and then Time to see standard clock-time examples. Choose one if it suits your data. To enter a specific display, choose Custom, click the Type field, and replace its contents with the format code you want. Do not type the code into the worksheet cell; it belongs in the format dialog.
Use the right code for clock times. For a 24-hour clock without leading zeros, enter h:mm; for a padded display such as 09:05, use hh:mm. To include seconds, use h:mm:ss. For a 12-hour clock with AM or PM, use h:mm AM/PM. Select a sample value in the dialog, if shown, to preview the display, then choose OK.
Use elapsed-hour formatting for durations. In the Custom Type field, enter [h]:mm and choose OK. The square brackets tell Excel to show the total hours rather than the hour portion of a clock. For example, a duration of 27 hours and 15 minutes displays as 27:15. To include seconds, use [h]:mm:ss. For a duration shown as total minutes and seconds, use [m]:ss.
Enter or check the values. Excel generally recognizes entries such as 8:30 AM, 20:30, or 0:45 as numeric times when the regional settings support that notation. Confirm a value is numeric by selecting it and checking that the formula bar shows a time value rather than text preceded by an apostrophe. If you are building a duration longer than a day directly, use a calculation rather than relying on how Excel interprets a typed value. For example, with D2 as the cell where you want to create 27 hours and 15 minutes, enter =27/24+15/1440. It returns 1.1354167 days as a stored value and displays 27:15 when D2 uses [h]:mm.
Format a total after adding durations. If C2:C4 contains duration values of 8:30, 7:45, and 10:15, select the cell for the total and apply [h]:mm. With the total in C5 and the source durations in C2:C4, enter =SUM(C2:C4). The result is 26:30, because 8 hours 30 minutes plus 7 hours 45 minutes plus 10 hours 15 minutes equals 26 hours 30 minutes. Without the brackets, a clock-style hour format may show 2:30 instead.
Calculate time between a start and end, if needed. With a start time in A2 and an end time in B2, enter =MOD(B2-A2,1) in C2, then format C2 as [h]:mm. For a shift from 10:00 PM to 6:30 AM, this returns 8:30 by treating the end as occurring after midnight. This method assumes the elapsed time is less than 24 hours. If your cells contain full date-and-time stamps or a period longer than a day, subtract the actual start value from the end value instead and format the result as [h]:mm.

Excel stores a day as 1, so one hour is 1/24 of a day and one minute is 1/1440. A time format changes only the display. That is why the same underlying value can appear as a clock reading, a duration, or a decimal if you apply different number formats.

Common Mistakes

Using h:mm for a long total. This format treats hours like a clock and can roll over after 24 hours. Apply [h]:mm to totals, billable durations, and accumulated work time.
Confusing time of day with duration. A start time of 9:00 AM is not the same kind of information as a nine-hour duration. Formatting alone cannot make a clock time into an elapsed amount; use a suitable calculation when you need the difference between two times.
Entering times as text. A leading apostrophe, pasted text, or a format Excel does not recognize can prevent calculations. Text may look like a time but will not add or subtract as expected.
Applying a time format to decimal hours without conversion. The number 2.5 formatted as a time is not automatically understood as 2 hours and 30 minutes. Excel reads 2.5 as two and a half days. Divide decimal hours by 24 before displaying them as hours and minutes.
Assuming formatting changes the value. Applying hh:mm does not round a stored value or remove seconds. It only hides parts of the display. The hidden seconds can still affect sums and comparisons.

Tips

Convert decimal hours when you need a time display. If A2 contains decimal hours, such as 2.5, enter =A2/24 in B2 and format B2 as [h]:mm. The result is 2:30. To convert back to decimal hours, with a duration in B2 enter =B2*24 in C2 and format C2 as Number; 2:30 returns 2.5. Use decimal values for arithmetic only when that is the unit your report needs.
Choose a consistent display for the whole column. If some entries appear as 8:05 and others as 08:05, a consistent custom format such as hh:mm makes lists easier to scan. Apply the format to the whole input range before entering new values.
Keep dates when the timing crosses days. If a task may last longer than 24 hours, record a start date and time as well as an end date and time. Subtracting full date-time values preserves the true duration; a time-only subtraction cannot distinguish a one-hour interval from one lasting a day and an hour.
Remember the seconds. If the source values contain seconds but the cells show only hours and minutes, totals can appear slightly unexpected. Display seconds with [h]:mm:ss when precision matters, or deliberately round the value if your reporting rules call for minute-level results.

Troubleshooting

The cell shows ####. Widen the column by dragging its right border or double-clicking the border between column letters to AutoFit. Excel may also show hashes when a date or time value is negative and the workbook uses the standard 1900 date system. Check the calculation and the order of the start and end times.
The total resets at 24 hours. Select the total cell, press Ctrl+1, choose Custom, and change the format to [h]:mm. Check that the source values are numeric durations and that the total is using SUM over the intended range.
The value displays as a decimal or a date. Reopen Format Cells and choose the intended time format. If the cell displays an unexpected date, remember that Excel’s stored time is a day fraction and a general number format exposes the underlying serial value. Formatting it as time changes the display, not the stored amount.
A value will not calculate. Select the cell and inspect the formula bar. If the value is text, remove any leading apostrophe, then re-enter it in a recognized time format. If it came from a website or CSV file, use Data > Text to Columns or convert the source data so Excel interprets it as a number. Check regional settings too: some systems expect a different time separator or AM/PM notation.
The displayed minutes seem rounded or off by one. The underlying value may include seconds or a small decimal remainder from a calculation. Temporarily display seconds with h:mm:ss or inspect the value in the formula bar. If the result should be rounded to a whole minute, apply an explicit rounding calculation based on the reporting requirement instead of relying on number formatting alone.

Frequently Asked Questions

How do I show more than 24 hours in Excel?
Use a custom number format of [h]:mm. The brackets preserve total elapsed hours, so a 30-hour duration displays as 30:00 rather than 6:00.

What is the difference between h:mm and [h]:mm?
h:mm displays hours as part of a clock and wraps after 24 hours. [h]:mm displays cumulative elapsed hours, which is usually the better choice for totals and durations.

How do I display AM and PM?
Select the cells, open Format Cells, choose Custom, and enter h:mm AM/PM. Use hh:mm AM/PM if you want a leading zero for single-digit hours.

How do I add hours and minutes in Excel?
Store each value as a numeric time or duration, add the cells with SUM, and format the result as [h]:mm if the total could exceed 24 hours. For example, with durations in C2:C4, use =SUM(C2:C4) in C5; if those values are 8:30, 7:45, and 10:15, the total is 26:30.

Why does 2.5 show as a date or a time I did not expect?
Excel treats 2.5 as two and a half days, not 2.5 hours. If A2 holds decimal hours, divide it by 24 with =A2/24, then format the result as [h]:mm to display 2:30.

Can I use these formats in Excel for Mac or the web?
Custom time codes such as [h]:mm are supported in current Excel versions. On Mac, use Command+1 to open Format Cells in current versions; in Excel for the web, use the Home tab’s Number Format controls, noting that some dialog options may vary.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.