Excel Sum Time and Format: A Complete Guide

Updated 4 November 2026 · Standard formula, shown below

This guide explains how to sum time values in Excel and format the result correctly, so you get accurate totals instead of confusing numbers. Youâ€™ll learn the essential formatting trick, common pitfalls, and how to handle durations over 24 hours.

Why Summing Time in Excel Is Tricky

Excel stores time as a fraction of a day. For example, 12:00 PM is stored as 0.5, and 6:00 AM as 0.25. When you sum time, Excel adds these decimal fractions, and the result can exceed 1 (i.e., 24 hours). By default, Excel displays the sum as a time of day, so if your total is 25 hours, it shows 1:00 AM instead of 25:00. This is the root of most confusion.

How to Sum Time in Excel

To sum time values, simply use the SUM function. For example, if you have time values in cells A1:A5, enter =SUM(A1:A5). This works fine, but the display may be wrong unless you format the cell correctly.

The key is to apply a custom number format that shows elapsed hours, not a clock time. Hereâ€™s how:

Select the cell with the sum.
Press Ctrl+1 to open Format Cells.
Go to the Number tab, select Custom.
In the Type box, enter [h]:mm for hours and minutes, or [h]:mm:ss for hours, minutes, and seconds.
Click OK.

The square brackets around h tell Excel to display total hours, even if they exceed 24. Without brackets, Excel resets to zero after 24 hours.

Formatting Time Durations vs. Time of Day

Understanding the difference between a duration and a time of day is crucial. A duration is an elapsed amount, like 25 hours. A time of day is a point on the clock, like 1:00 AM. Excel uses the same underlying number for both, but the display format determines which one you see.

Time of day: Use h:mm AM/PM or hh:mm (e.g., 1:00 AM).
Duration: Use [h]:mm or [h]:mm:ss (e.g., 25:00).

For durations, always include the square brackets. This is the single most important formatting tip for summing time.

Common Problems and Fixes

Problem: Sum shows 0:00 or a small number

This usually means the cells arenâ€™t recognized as time. Check that the cells contain actual time values (e.g., 8:30) and not text. You can test by using =ISNUMBER(A1); if it returns FALSE, the cell is text. Convert text to time using =TIMEVALUE(A1) or re-enter the values.

Problem: Sum shows a time of day like 2:00 AM

This is the classic issue. Apply the custom format [h]:mm as described above.

Problem: Negative time values

Excel canâ€™t display negative time by default. If you subtract times and get a negative result, youâ€™ll see #####. To fix, use the 1904 date system (File > Options > Advanced > â€œUse 1904 date systemâ€�) or ensure your formula always returns a positive value, e.g., with =MAX(0, end-start).

Using Time Formats in Formulas

When you sum time, the result is a decimal number. For example, 1:30 + 2:45 = 4:15, which Excel stores as 0.177083 (4.25 hours / 24). To convert the sum to decimal hours, multiply by 24: =SUM(A1:A5)*24. This is useful for payroll or billing. Similarly, multiply by 1440 to get minutes.

Example: If your sum is 25:00 (stored as 1.041667), then =SUM(A1:A5)*24 returns 25.

How to Choose the Right Time Format

Choosing the right format depends on what you need to show:

For a work schedule: Use [h]:mm to show total hours worked.
For a project timeline: Use [h]:mm:ss if you need seconds.
For a clock time: Use h:mm AM/PM or hh:mm.
For decimal hours: Use 0.00 and multiply by 24.

Also, consider locale: some countries use a 24-hour clock, others use AM/PM. Excelâ€™s default time format adapts to your system settings, but custom formats override that.

Alternatives to Summing Time

If you need to sum time across multiple rows or columns, you can use the SUM function with a range. For conditional sums, use SUMIF or SUMIFS. For example, to sum only times on a specific date, use =SUMIFS(C:C, A:A, date).

If youâ€™re working with large datasets, consider using a PivotTable. It can sum time values and display them with the same custom format, but you must apply the format to the value field.

Frequently Asked Questions

Why does my sum of time show 0:00?

Your cells likely contain text, not time values. Check with =ISNUMBER(A1). If itâ€™s text, convert it using =TIMEVALUE(A1) or re-enter as time.

How do I sum time over 24 hours?

Apply the custom format [h]:mm to the sum cell. The square brackets allow the hour count to exceed 24.

Can I sum time in Excel and get decimal hours?

Yes. After summing, multiply the result by 24 and format the cell as a number. For example, =SUM(A1:A5)*24 gives total hours as a decimal.

What does [h]:mm mean in Excel?

Itâ€™s a custom number format. The brackets around h tell Excel to display total elapsed hours, even if they exceed 24. Without brackets, Excel resets to 0 after 24 hours.

Why does Excel show ##### for my time sum?

This usually means the column is too narrow to display the value, or the time is negative. Widen the column or fix negative values. For negative times, enable the 1904 date system.

How do I subtract time and get a positive result?

Use a formula like =MAX(0, end_time - start_time) to avoid negative results, or ensure the end time is always later.

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.