How to sum and format a range of hours, minutes, or seconds in Excel

Article translations Article translations
Article ID: 266691 - View products that this article applies to.
This article was previously published under Q266691
Expand all | Collapse all

On This Page

Summary

To automatically format times that when added together exceed 24 hours, 60 minutes, or 60 seconds, you can use the following custom number formats and the SUM function in Excel:
  • Hours = [h]:mm:ss
  • Minutes = [m]:ss
  • Seconds = [ss]

More information

In Microsoft Excel, the custom hours format is already created. However, you must create the minutes and seconds formats for each workbook that needs them.

To correctly display hour totals greater than 24, and minute and second totals greater than 60, use the following methods.

Example for Hours

To format time sums that are greater than 24 hours, follow these steps:
  1. Create a new worksheet and type the following values. Make sure to use a colon (:) to separate the numbers.
         A1:      12:35:58
         A2:      12:24:30
         A3:      11:18:23
    					
  2. Type the following formula into cell A4. Excel automatically formats the result correctly if you are adding hours.
         A4:      =SUM(A1:A3)     
    					
Excel displays 36:18:51 as the total.

Example for Minutes and Seconds

To format minute or second sums that are greater than 60, follow these steps:
  1. Create a new worksheet and type the following values. Make sure to insert the leading zeros (00), and use a colon (:) to separate the numbers.
         A1:      00:42:58
         A2:      00:19:30
         A3:      00:37:23
    					
  2. Type the following formula in cell A4:
         A4:      =SUM(A1:A3)     
    						
    Excel displays 1:39:51 as the total. To display this total as minutes and seconds, or seconds only, follow these steps to create and apply the appropriate format:
    1. Right-click cell A4, and then click Format Cells.
    2. Click the Number tab.
    3. In the Category list, click Custom.
    4. In the Type box, edit the number format codes to create the format you want.
      • For the minutes format, type [m]:ss
      • For the seconds format, type [ss]
    5. Select the formatting that you want to apply, and then click OK.
    After you apply the minutes format, Excel displays 99:51 as the total.

    After you apply the seconds format, Excel displays 5981 as the total.
NOTE: When you edit a built-in format, Excel creates a new format but does not replace the existing format.

How to Apply the Formats to Additional Cells

After you create the custom minutes and seconds formats, follow these steps to apply them to any additional cells in the workbook:
  1. Right-click the cells that you want to display the newly created format, and then click Format Cells.
  2. In the dialog box, click the Number tab, and then click Custom.
  3. In the custom list of formats, click to select the appropriate format, and then click OK.

Properties

Article ID: 266691 - Last Review: October 25, 2012 - Revision: 9.0
Applies to
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Excel 2000 Standard Edition
  • Microsoft Office Excel 2003
  • Microsoft Office Excel 2007
  • Microsoft Excel 2010
  • Microsoft Excel 2013
Keywords: 
kbhowto KB266691

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com