How to Add Hours in Excel: Formulas and Time Formats

Formulas & functions · Updated November 20, 2026

To add hours in Excel, enter each duration in a cell, total the cells with SUM, then format the result as [h]:mm so hours keep accumulating past 24. This works for timesheets, shift totals, and other elapsed-time calculations. You will need a worksheet with the durations you want to add; the steps below use Excel for Microsoft 365 on Windows.

What You’ll Need

  • An Excel worksheet with each duration in its own cell, ideally in one column.
  • A consistent way to enter durations, such as hours and minutes (for example, 7:30 for seven hours and thirty minutes).
  • Excel for Microsoft 365 on Windows. Most steps also work in Excel for Mac and Excel for the web, although some formatting controls are arranged differently.

Step-by-Step

  1. Set up a column for the durations. Put a label such as Hours in A1, then enter each elapsed time below it. For example, enter 7:30 in A2, 8:15 in A3, 6:45 in A4, and 9:00 in A5. Excel stores times as portions of a day, so these entries represent durations that can be added. If you are preparing a timesheet, put one shift or task duration in each row and keep headings or notes outside the range you plan to total.

  2. Enter durations consistently. Type hours, a colon, and minutes, including minutes from 00 through 59. For example, 6:05 means six hours and five minutes, not six hours and five hundredths of an hour. If Excel displays an entry with AM or PM, it may have applied a time-of-day format; the underlying value can still be added, but you should apply a duration format in the next step. Avoid typing decimal hours such as 7.5 as if they were 7 hours and 30 minutes: Excel reads 7.5 as seven and a half days unless you convert it.

  3. Choose the cell where the total should appear. If your entries are in A2:A5, select A6, or another blank cell below the list. Leave the total cell outside the range being summed. This makes the result easier to identify and prevents a circular reference, which happens when a formula includes its own cell.

  4. Add the durations with SUM. With durations in A2:A5 and the total in A6, enter =SUM(A2:A5) in A6 and press Enter. The sample values total 31 hours and 30 minutes: 7:30 + 8:15 + 6:45 + 9:00 = 31:30. SUM is useful because it ignores blank cells and adjusts naturally if you include additional rows in the range. You can also select the cells and choose Home > AutoSum, then check that Excel selected the intended range before confirming.

  5. Format the total to show more than 24 hours. Select the total cell, press Ctrl+1 to open Format Cells, choose Number > Custom, and enter [h]:mm in the Type box. Select OK. The square brackets tell Excel to display cumulative hours instead of restarting the hour count at each 24-hour day. With the example above, the total appears as 31:30. Without brackets, a time format such as h:mm can display 7:30 for that same total because it shows only the time remaining within a day.

  6. Format individual entries if they look inconsistent. Select the duration cells, press Ctrl+1, and choose a suitable format. For entries that should show hours and minutes, a custom format such as [h]:mm also works for the individual cells. If you want to show leading zeroes for minutes, use [h]:mm, which displays 6:05 rather than 6:5. Applying a consistent format changes how values appear, not the stored duration.

  7. Check the result against the inputs. Add a few entries mentally or with a calculator and compare the result. In the example, the first two entries total 15:45, the next two total 15:45, and those subtotals make 31:30. If the total is unexpectedly small, verify its number format before changing the formula; a display of 7:30 may be a valid 31:30 total shown with a format that wraps after one day.

  8. Use a separate calculation for decimal-hour reports, if needed. Some payroll or project reports ask for a number such as 31.5 hours instead of 31:30. With the durations in A2:A5 and the result in A6, use =SUM(A2:A5)*24. The example returns 31.5 because 30 minutes is half an hour. Format this result as General or Number, not as a time. Keep the underlying duration total in a separate cell if you also need to display hours and minutes.

To add an hour amount to an existing duration: You can add two duration cells together, or convert a decimal number of hours into Excel’s time units. With an existing duration in A2 and another duration in B2, enter =A2+B2 in C2; if A2 is 7:30 and B2 is 2:15, C2 is 9:45. Format C2 as [h]:mm if the result might exceed a day. If A2 contains a duration and B2 contains a decimal number of hours, such as 2.5, enter =A2+B2/24 in C2. When A2 is 7:30, the result is 10:00. Dividing the decimal hours by 24 converts them to the day-based value Excel uses for time.

Common Mistakes

  • Using h:mm for a cumulative total. A total of 31 hours and 30 minutes may appear as 7:30 if the cell uses a clock-style format. The value has not necessarily been lost; use the custom format [h]:mm to show the full accumulated hours.

  • Mixing decimal hours with hours-and-minutes entries. The value 7.5 entered as a number is not the same as a duration of 7 hours and 30 minutes in Excel. Convert decimal hours by dividing by 24 when creating a time value, or multiply a duration by 24 when reporting a decimal-hour total.

  • Entering minutes as hundredths. Thirty minutes is 0.5 of an hour, not 0.30 hours. Enter 7:30 for seven hours and thirty minutes, or enter 7.5 as a decimal-hour amount and convert it appropriately.

  • Including the total cell in its own SUM range. A formula in A6 that sums A2:A6 includes A6 itself. Use A2:A5, or adjust the range when adding rows, so the result cell is not part of its own calculation.

  • Using a start time where a duration is expected. 9:00 AM is a time of day, while 9:00 may be intended as nine elapsed hours. Excel can add both kinds of values, but unclear labels and mixed data make totals confusing. Label columns explicitly, for example Start, End, Break, and Hours Worked.

Tips

  • Apply [h]:mm to the total cell before entering a long list of data. That way, totals over 24 hours display correctly as soon as they are calculated.

  • Use one row per duration and keep text such as notes or employee names in a separate column. This makes it easier to select a clean range and check which entries are included.

  • For a reusable timesheet, place the total formula below the last entry and leave a spare row or use a clearly defined range. If you turn the data into an Excel table with Insert > Table, you can add rows more easily, but still confirm that the total formula includes them.

  • To enter a duration longer than 24 hours, do not rely on typing a clock-style value that Excel might interpret unexpectedly. If a single entry represents 27 hours, enter a formula such as =27/24 in the cell, then apply [h]:mm; it displays 27:00. For routine shifts under a day, entering hours and minutes such as 8:15 is simpler.

  • Keep precision in mind when converting to decimal hours. A duration of 1:20 is 1.3333 hours, not 1.2 hours. If a report needs a particular number of decimal places, format the decimal result to the required number rather than rounding each original shift before totaling.

  • On Mac, use Command+1 to open Format Cells in place of Ctrl+1 on Windows. In Excel for the web, use the Home tab’s number-format controls; depending on the interface, custom-format choices may be under More Number Formats. The same [h]:mm format is the key to displaying cumulative hours.

Troubleshooting

The total looks like it has lost 24 hours. Select the total cell and inspect its number format. If it uses h:mm, change it to [h]:mm through Format Cells > Number > Custom. The bracketed h displays the full accumulated count rather than the clock hour within a day.

The formula appears as text or does not calculate. Check that the cell does not have the Text number format and that the formula begins with an equals sign. Change the cell to General, re-enter the formula, and press Enter. Also check for a leading apostrophe before the equals sign, which makes Excel treat the formula as text. If formulas remain visible throughout the sheet, toggle Show Formulas off with Ctrl+` on Windows; the key is usually above Tab.

The answer is a decimal or a date instead of hours and minutes. The formula may be correct but the result cell has a General, Number, or Date format. For an elapsed-time display, apply [h]:mm. For a decimal-hour result, multiply the summed time by 24 and use General or Number. Choose the display that matches the report you need.

Excel will not accept an entry or the total is too large. Check that minutes are between 00 and 59, and confirm that the cells contain numeric time values rather than text pasted from another source. Re-enter a sample duration directly, then test the SUM formula on that cell. To check whether a value is stored as a number, select it and look at the formula bar and alignment; numbers normally align right by default, although formatting can change alignment.

A shift crosses midnight. If you are calculating elapsed time from a start and end time, ordinary subtraction can produce a negative result when the end time is after midnight. With a start time in A2 and an end time in B2, use =MOD(B2-A2,1) in C2. For a shift from 10:00 PM to 6:00 AM, it returns 8:00. Format C2 as [h]:mm. This calculation assumes the shift is less than 24 hours; for longer shifts, include the date as well as the time so the elapsed duration is unambiguous.

Frequently Asked Questions

How do I add hours and minutes in Excel?
Enter each duration in a cell using hours:minutes, then total the range with SUM. Format the result as [h]:mm if the sum could reach 24 hours or more.

Why does my Excel total restart at zero after 24 hours?
A clock-style number format displays time within a 24-hour day. Change the total’s custom format to [h]:mm to show cumulative elapsed hours.

Can I add decimal hours, such as 1.5 hours?
Yes. If the amount is stored as a decimal number, divide it by 24 before adding it to a time value. To turn a duration total into decimal hours, multiply the duration by 24 and display the result as a number.

How can I total hours when a shift ends the next day?
Use MOD on the end time minus the start time, then format the result as [h]:mm. For shifts that may last 24 hours or longer, include the actual start and end dates in the calculation.

Can SUM add blank cells in a timesheet range?
Yes. SUM ignores blank cells, so you can total a column that has not been completely filled in. Check that every completed entry is a real Excel time value and that the chosen range covers all the rows you intend to include.

Should I use [h]:mm or [h]:mm:ss?
Use [h]:mm when hours and minutes are sufficient. Use [h]:mm:ss when seconds matter, such as timing short tasks. The brackets around h are what prevent the displayed hour count from wrapping after 24 hours.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

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

Spotted an error in this guide? Let us know.