XL: Working with Time Periods That Exceed 24 Hours

ID: Q99349

The information in this article applies to:

SUMMARY

In Microsoft Excel versions earlier than version 5.0, you must follow a special procedure to sum amounts of time that exceed 24 hours. This is because of the way Microsoft Excel calculates and displays time.

In Microsoft Excel 98 Macintosh Edition, 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 that collectively exceed 24 hours, use the appropriate formula below.

Decimal Output

To add times with a sum that exceeds 24 hours, use the following array formula: 


   =SUM(Range*24)


NOTE: The above formula must be entered as an array formula. To enter a formula as an array formula in Microsoft Excel for Windows or Microsoft Excel for OS/2, press CTRL+SHIFT+ENTER. In Microsoft Excel for the Macintosh, press COMMAND+ENTER. (Additional information about entering an array formula is available in Help. On the Help menu, click Search for Help and type "array formula" in the dialog box. Click the Show Topics button to view the available topics.)

Or, enter the following formula as a regular worksheet formula: 


   =SUM(Range)*24


NOTE: A cell that contains either of these formulas should be formatted in either the General format or in a decimal format, not in a time format.

Text Output

If you want to add times when the total exceeds 24 hours, and you want the total to appear in the "h:mm" format, use the following formula: 


   =TRUNC(SUM(Range*24))&":"&TEXT(MOD(SUM(Range*24),1)*60,"00")


NOTE: This formula must be entered as an array formula. To enter a formula as an array formula in Microsoft Excel for Windows or Microsoft Excel for OS/2, press CTRL+SHIFT+ENTER. In Microsoft Excel for the Macintosh, press COMMAND+ENTER.

Text Output in h:mm:ss Format

The following formula displays the result of the addition of times in h:mm:ss format as a text string: 


   =TRUNC(SUM(Range*24))&":"&TEXT(MINUTE(SUM(Range)),"00")&":"&
      TEXT(SECOND(SUM(Range)),"00")


NOTE: This formula must be entered as an array formula. To enter a formula as an array formula in Microsoft Excel for Windows or Microsoft Excel for OS/2, press CTRL+SHIFT+ENTER. In Microsoft Excel for the Macintosh, press COMMAND+ENTER.

Time Output in [h]:mm:ss Format (Microsoft Excel 5.0 and later)

In Microsoft Excel 5.0, 7.0, and Microsoft Excel 97, you can add times with a sum that exceeds 24 hours by using a normal sum formula: 


   =SUM(Range)


Then, click Cells on the Format menu, click the Number tab, and choose the following number format: 


   [h]:mm:ss


This format works correctly with times that exceed 24 hours.

NOTE: In all five of the above formulas, 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.


MORE INFORMATION

How Microsoft Excel Interprets Times

Microsoft Excel uses a serial numbering system to calculate dates and times. In this system, a single day is equal to one unit of time. Because an hour is equal to one twenty-fourth of a day, the serial number equivalent 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 is 0.0006944 (1 day divided by 1,440 minutes in a day), and the serial number equivalent of one second is 1.1574E-05 (1 day divided by 86,400 seconds in a day).

When you add times in Microsoft Excel, the serial number equivalents of the times are added and the result is displayed in a time format. However, only the fractional amount of the result (time less than 24 hours) can be displayed in a time format. As a result, your answer may be incorrect by a multiple 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 is 14: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 decimal point (1) is in days, and is therefore not displayed when a built-in time format is used. When formatted using the h:mm or hh:mm format, .6042 is displayed as "14:30" (without the quotation marks).

The following three examples use the formulas described in the "Summary" section of this article to accumulate times over 24 hours.

Example Using Decimal Output

Using the above example times, if you enter the formula 


   =SUM(A1:A4*24)


as an array formula, or the regular formula 


   =SUM(A1:A4)*24


you will receive 38.5 as a result. The integer portion of the result, 38, represents the number of hours; and the fractional portion, .5, represents the fraction of an hour (.5 * 1 hour = 30 minutes).

You can use this number in other computations, if necessary, but it is not possible to display this number in an "h:mm" or "hh:mm" format without using another formula to convert the number into text.

Example Using Text Output

Again using the above sample times, if you enter the formula 


   =TRUNC(SUM(A1:A4*24))&":"&TEXT(MOD(SUM(A1:A4*24),1)*60,"00")


as an array formula, you will receive the text string "38:30" as a result. Although this has the appearance of a number that uses the "h:mm" format, it is not a number; that is, you cannot perform computations with this number.

Example Using [h]:mm:ss Output

Again using the above sample times, if you enter the formula 


   =SUM(A1:A4)


and then format the cell with the number format "[h]:mm:ss", the cell will display a properly formatted time of 38:30. This is a proper time and can be used in other computations.

Additional query words: 2.00 2.01 2.10 2.20 2.21 4.00 5.00a 5.00c 8.00 97 XL3 XL4 XL5 XL7 XL97 accumulate 


Keywords          : kbdta xlformula 
Version           : WINDOWS:2.0,3.0,4.0,5.0,7.0,97; MACINTOSH:2.0,3.0,4.0,5.0,5.0a,98
Platform          : MACINTOSH WINDOWS 
Issue type        : kbinfo

Last Reviewed: April 6, 1999