You are currently offline, waiting for your internet to reconnect

Working with time periods that exceed 24 hours in Excel for Mac

This article was previously published under Q190633
In Microsoft Excel, you can format a cell containing the sum of amounts of time that exceed 24 hours with the number format "[h]:mm:ss"; Microsoft Excel automatically adds times beyond 24 hours and formats them correctly.
To add times with a sum that exceeds 24 hours, use a normal sum formula:
Then, click Cells on the Format menu, click the Number tab, click Custom in the Category list, and choose the following number format:
This format works correctly with times that exceed 24 hours.

Note Range is either the defined name of a range that contains the times or the cell reference of the range that contains your times (for example, A1:A4). The values in the range must be in the "h:mm" or "hh:mm" format.

How Microsoft Excel interprets times

Microsoft Excel uses a serial numbering system to calculate dates andtimes. In this system, a single day is equal to one unit of time. Becausean hour is equal to one twenty-fourth of a day, the serial numberequivalent of one hour is 0.041667 (1 day divided by 24 hours in a day).Following the same logic, the serial number equivalent of one minute is0.0006944 (1 day divided by 1,440 minutes in a day), and the serial numberequivalent of one second is 1.1574E-05 (1 day divided by 86,400 seconds ina day).

When you add times in Microsoft Excel, the serial number equivalents of thetimes are added and the result is displayed in a time format. However, onlythe fractional amount of the result (time less than 24 hours) can bedisplayed in a time format. As a result, your answer may be incorrect by amultiple of 24 hours. For example, if you add the following times
   Cell       Time     Serial equivalent (days)   ----       -----    ------------------------   A1         8:00     0.3333   A2         9:00     0.375   A3         10:00    0.4167   A4         11:30    0.4792              -----    ------   Expected   Total      38:30    1.6042				
the expected result is 38:30. However, the result that is displayed is14:30. This is because only the amount to the right of the decimal point(.6042) is used by the time format. The amount to the left of the decimalpoint (1) is in days, and is therefore not displayed when a built-in timeformat is used. When formatted using the h:mm or hh:mm format, .6042 isdisplayed as 14:30. Follow these steps tosee the expected total of "38:30":
  1. Select the cell that contains 14:30.
  2. On the Format menu, click Cells, and then click the Number tab.
  3. In the Category list, click Time. In the Type list, click 37:30:55. Click OK.
This is a proper time and can be used in other computations.

Excel X and later versions

For more information about time formats, click Excel Help on the Help menu, type time formats, click Search, and then click a topic to view it.

Excel 2001

For more information about time formats, click the Office Assistant, type formats for dates and times, click Search, and then click a topic to view it.

Note If the Assistant is hidden, click the Office Assistant button on the Standard toolbar.

Excel 98

For more information about time formats, follow these steps:
  1. On the Help menu, click Contents and Index. If you are using a version of the Macintosh operating system that is earlier than 8.0, click Contents and Index on the Balloon Help menu.
  2. In Excel Help, click Index, and then type the following text:
    time formats
  3. Click Show Topics. Click the Custom number, date, and time format codes topic, and then click Go To.
If you cannot find the information that you want, ask the Office Assistant.

XL2001 XL98 accumulate XL2004 XLX

Article ID: 190633 - Last Review: 10/06/2011 04:29:00 - Revision: 3.0

  • Microsoft Excel 2004 for Mac
  • Microsoft Excel X for Mac
  • Microsoft Excel 2001 for Mac
  • Microsoft Excel 98 for Macintosh
  • kbhowto KB190633