Excel Time Format: Show Hours and Minutes Only Correctly

To show hours and minutes in Excel, select the cells, press Ctrl+1, choose Custom, and enter h:mm (or hh:mm for a leading zero). For totals that can exceed 24 hours, use [h]:mm instead; the brackets prevent Excel from restarting the displayed hours at zero after a full day.

Choose the right hours-and-minutes format

The best format depends on whether your values represent a time on the clock or an amount of elapsed time. These formats all display hours and minutes, but they do not mean the same thing:

h:mm displays hours without a leading zero, such as 7:05 or 19:05. It is useful for ordinary times in a 24-hour display.
hh:mm always displays a two-digit hour, such as 07:05 or 19:05. Use it when you want every time to line up visually.
[h]:mm displays total elapsed hours, including values above 24. A duration of 31 hours and 5 minutes appears as 31:05 rather than 7:05.
h:mm AM/PM displays a 12-hour clock, such as 7:05 AM or 7:05 PM. It includes an AM or PM marker, so it is not a minutes-only display.

If the request is simply to show a time such as 9:30, start with h:mm. If a result is a work duration, travel time, or accumulated total, check whether it can reach 24 hours; if so, choose [h]:mm. The brackets are the key difference for a total.

Apply a custom format in Excel for Windows

In Excel for Microsoft 365 on Windows, you can apply a built-in time format or type a custom format code. The custom option is useful when you specifically want hours and minutes without seconds.

Select the cells you want to format. To format an entire column, click its column letter.
Press Ctrl+1 to open Format Cells. Alternatively, right-click the selection and choose Format Cells.
On the Number tab, select Custom in the Category list.
Type h:mm, hh:mm, or [h]:mm in the Type box, as appropriate, and select OK.

Custom formats affect how a value appears; they do not replace the value or change its underlying precision. You can change the format again later without re-entering the time. To display minutes and seconds too, use a format such as h:mm:ss; to show seconds with a duration above 24 hours, use [h]:mm:ss.

On Excel for Mac, select the cells and use Command+1 to open the Format Cells dialog; then choose Custom and enter the format code. The exact dialog layout can vary by Excel release. Excel for the web can display many custom formats, but its formatting controls may differ from the desktop dialog. If you cannot create or edit the custom code in your browser, open the workbook in desktop Excel and set it there.

How Excel stores time values

Excel stores a time as a fraction of a day, not as a text label. For example, noon is one-half of a day, and 6:00 AM is one-quarter of a day. A date-time value can also include a whole-number date portion and a fractional time portion. A number format tells Excel how to display that stored number.

This distinction explains why changing a cell to h:mm can hide seconds without removing them. A cell containing 8:15:42 may display as 8:15, but calculations still use the seconds. If you add several such values, hidden seconds can affect the final total. Formatting is therefore a display choice, not a rounding operation.

It also explains why typing a time into a cell formatted as Text may not behave like entering a real time. Excel may keep the characters as text, which usually will not respond to a number format or participate in time arithmetic as expected. If a format appears to have no effect, first check that the cell contains a numeric time value rather than text.

Clock times and elapsed durations are different

A clock time identifies a point in the day: 8:30 AM or 20:30. An elapsed duration measures how long something lasted: 8 hours and 30 minutes. The distinction matters when results cross midnight or when you add many durations together.

With a clock-time format such as h:mm, Excel displays the hour within the daily clock. A sum of 26 hours can consequently appear as 2:00, because the display has gone around the clock. That does not necessarily mean the sum is wrong; the format may be unsuitable for an elapsed total. [h]:mm displays accumulated hours instead, so the same amount appears as 26:00.

For a shift from 10:00 PM to 6:30 AM, the finish time is earlier on the clock than the start time. With the start time in A2 and the finish time in B2, enter this in C2 to calculate a positive overnight duration:

=MOD(B2-A2,1)

For those example inputs, the result is 8:30. Format C2 as [h]:mm or h:mm; either shows that particular duration correctly. The MOD calculation treats the difference as wrapping around a 24-hour day, so it assumes the interval is less than 24 hours. If shifts can last 24 hours or more, a date as well as a time is needed to distinguish the correct start and end.

Display totals above 24 hours

Elapsed-time columns commonly use a sum at the bottom. If the total should represent all hours worked or all time spent, format the total cell as [h]:mm. Square brackets tell Excel to display cumulative hours rather than cycling through the daily clock.

For example, with durations of 1:45, 2:20, and 0:55 in C2:C4, enter this in C5 to add them:

=SUM(C2:C4)

The result is 5:00, because 1 hour 45 minutes plus 2 hours 20 minutes plus 55 minutes equals 5 hours. With larger totals, the bracketed format becomes essential: 18:00 plus 9:00 is 27 hours, which [h]:mm displays as 27:00. A regular h:mm display shows 3:00, masking the fact that the total exceeded a day.

For a table of durations, apply the format to both the individual duration cells and the total if you want consistent display. A sum cell does not always inherit the format you intended from the cells above it, especially if it was created or pasted separately.

Show minutes without seconds or dates

Use h:mm when seconds should be omitted from the display. If a cell currently shows a date as well as a time, a time-only format can hide the date portion. The date is still stored in the cell, however. This can be useful for a schedule, but it can also conceal information if the date matters to the calculation.

If you need a 12-hour clock, include the AM/PM marker, as in h:mm AM/PM. Without that marker, a value displayed as 7:05 does not tell a reader whether it is morning or evening. For a 24-hour clock, hh:mm makes the hour consistently two digits, so 7:05 AM appears as 07:05 and 7:05 PM as 19:05.

Excel interprets the letter m as minutes in a time context such as h:mm. In other date formats, m can represent a month. If a custom format combines dates and times and the result looks unexpected, review the full format code and use a clear time pattern. A plain h:mm is the straightforward choice when the goal is only hours and minutes.

Common problems and how to fix them

The total shows a small number of hours. If the total exceeds 24 hours, change the total cell to [h]:mm. The ordinary clock format wraps the displayed hour.
Changing the format does nothing. The entry may be text rather than a numeric time. Check how it is aligned and whether it behaves as a number in calculations; re-entering a valid time into a General or time-formatted cell may resolve it.
Seconds still affect the total. A format that omits seconds only hides them. If you need the data itself rounded to whole minutes, use a rounding calculation or change the source values; do not rely on display formatting to remove precision.
A result appears as a row of number signs. The column may be too narrow, so widen it first. A negative time can also display this way under Excel’s default date system. Check the calculation and whether negative durations are possible before changing workbook date-system settings.
An overnight calculation is negative. Subtracting a later clock time from an earlier one crosses midnight. Use the MOD approach for a single overnight interval, or include actual dates when the duration may span more than one day.

Do not switch a workbook’s date system casually to address a display problem. Date-system changes can affect date values throughout the workbook. For most duration totals, selecting the right format and checking the underlying time calculation are safer first steps.

Frequently Asked Questions

What is the Excel format for hours and minutes only?
Use h:mm for a normal time such as 9:05. Use hh:mm when you want a leading zero, as in 09:05. Both omit seconds from the display.

How do I stop Excel time totals from resetting after 24 hours?
Format the total cell with the custom code [h]:mm. The square brackets display total elapsed hours, so 27 hours appears as 27:00 rather than 3:00.

Does h:mm remove seconds from the cell?
No. It hides seconds in the display but leaves the stored value unchanged. Hidden seconds can still affect sums and other calculations.

How do I show 07:05 instead of 7:05?
Apply the custom format hh:mm. The doubled hour code requests a leading zero for a one-digit hour.

How do I calculate a shift that ends after midnight?
If the start and finish cells contain clock times and the interval is under 24 hours, use =MOD(B2-A2,1) with the start in A2 and finish in B2. Format the result as hours and minutes. For longer intervals, use full date-and-time entries so Excel can calculate the elapsed days as well.

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.