The basic formula for adding hours and minutes

To add up time in Excel, use the SUM function the same way you would add numbers. Type =SUM(, then select the cells containing your times, then close with ). Excel will add them together and show you the total.

The catch is that Excel stores time as a decimal fraction of a 24-hour day. When you add times that total more than 24 hours, Excel may show only the remainder — for example, 30 hours might display as 6 hours. To fix this, format the cell as a number instead of a time format, or use a formula that converts the result back to hours and minutes in a readable form.

Key Takeaways

  • The SUM function adds time cells just like it adds numbers: =SUM(A1:A5) totals all times in that range.
  • Times over 24 hours need special formatting to display correctly — either change the cell format to a number or use a formula that shows hours beyond 24.
  • If your times are stored as text instead of actual time values, SUM will ignore them and you will need to convert them first.
  • Breaking time into hours and minutes as separate columns makes the math simpler and avoids formatting problems altogether.

Setting up your time data so Excel recognizes it

Excel only adds times correctly if it recognizes them as time values, not text. When you type a time, use a format Excel understands: 2:30 PM, 14:30, or 2:30. If you paste times from another source or type them inconsistently, Excel may treat them as text and refuse to add them.

To check whether a cell contains a time value or text, click on it and look at the formula bar at the top. If the cell shows the time but the formula bar shows it as text (often with a small green triangle in the corner), you have a text problem. Select those cells, go to the Data menu, choose Text to Columns, click Next twice, and then Finish. This converts text that looks like time into actual time values Excel can use.

Using SUM to add a column of times

The simplest case is a column of times you want to total. Click on an empty cell below your times. Type =SUM(A1:A10), replacing A1:A10 with the actual range of cells that hold your times. Press Enter. Excel adds them up.

If the result shows a time like 5:30 but you know the total should be 29 hours and 30 minutes, the cell is formatted as time and is hiding the hours beyond 24. Right-click the cell, choose Format Cells, select Number from the Category list, and set Decimal places to 2. The result will show as 29.50 (meaning 29 hours and 30 minutes). Alternatively, you can keep the time format and use a formula instead: =INT(SUM(A1:A10))*24+HOUR(SUM(A1:A10))&":"&TEXT(MINUTE(SUM(A1:A10)),"00"), which displays the total as hours and minutes no matter how large.

Adding times when you have hours and minutes in separate columns

If your spreadsheet has hours in one column and minutes in another, add them separately and then combine the result. For example, if hours are in column A and minutes are in column B, use =SUM(A1:A10)&" hours "&SUM(B1:B10)&" minutes". This shows the total as text like "47 hours 45 minutes".

If you want a single time value instead, convert the total minutes to hours and minutes first. Use =SUM(A1:A10)+(SUM(B1:B10)/60)/24. This adds the hours, converts the minutes to a fraction of a day (which is how Excel stores time), and gives you a result you can format as time. Then format the cell as time and it will display correctly.

Handling times that cross midnight or span multiple days

If your times include entries like 11:00 PM to 2:00 AM (crossing midnight), or if you are tracking work across multiple days, store the data as hours and minutes in decimal form rather than as clock times. For example, use 8.5 for 8 hours 30 minutes instead of 8:30. This avoids the confusion of whether 2:00 AM is the same day or the next day.

If you must use clock times, add a separate column for the date or day number, and calculate the elapsed time using a formula that accounts for the date change. For a shift from 11:00 PM on one day to 2:00 AM the next, enter the start time as 11:00 PM and the end time as 2:00 AM on the next date. Then use =SUM(end time - start time) and format as time. Excel will show 3:00 (3 hours) correctly.

Converting decimal hours to hours and minutes format

If you have times stored as decimals — 8.5 for 8 hours 30 minutes, or 2.75 for 2 hours 45 minutes — and you want to display them as hours and minutes, use a formula. In a new cell, type =INT(A1)&":"&TEXT((A1-INT(A1))*60,"00"), where A1 is the cell with the decimal time. This shows 8.5 as 8:30 and 2.75 as 2:45.

If you want to add a column of decimal times and show the result as hours and minutes, combine the two steps: =INT(SUM(A1:A10))&":"&TEXT((SUM(A1:A10)-INT(SUM(A1:A10)))*60,"00"). This adds all the decimal times and displays the total in hours:minutes format.

Troubleshooting when SUM does not work

If you type a SUM formula and it returns 0 or shows an error, the most common cause is that your times are stored as text. Use the Text to Columns method described above to convert them. Another cause is that you have included a header row in your range — for example, =SUM(A1:A10) when A1 contains the word "Time". Excel ignores text in SUM, so this usually works, but if you get an error, start your range at A2 instead.

If the result shows a time but it is clearly wrong — for example, 5:30 when you expected 29:30 — the cell is formatted as time and is showing only the remainder after 24 hours. Change the format to Number as described above, or use the formula that displays hours beyond 24. If you see ##### symbols in the cell, the column is too narrow to display the result. Double-click the border between the column headers to auto-fit the width.

Frequently Asked Questions

Why does my total show 5:30 when I added up 29 hours and 30 minutes?

Excel stores time as a fraction of a 24-hour day. When the total exceeds 24 hours, the time format shows only the remainder. Right-click the cell, choose Format Cells, select Number, and set decimal places to 2. The result will show as 29.50 instead of 5:30.

Can I add times that are stored as text like "2 hours 30 minutes"?

Not with SUM. You would need to convert them to a number format first. The easiest way is to split them into separate columns for hours and minutes, convert each to a number, then add those columns. Or retype them in Excel's time format (2:30) so Excel recognizes them as time values.

How do I add times from different cells that are not in a single column or row?

List all the cells inside the SUM parentheses, separated by commas: =SUM(A1,C5,E10). Or use multiple ranges: =SUM(A1:A5,C1:C5). Excel adds all of them together regardless of where they are on the sheet.

What if I want to add times but also subtract some?

Use addition and subtraction in the same formula: =SUM(A1:A5)-SUM(B1:B5). This adds all times in A1 through A5, then subtracts all times in B1 through B5. Format the result as time or number depending on whether you expect the answer to exceed 24 hours.