XL98: Working with Time Periods That Exceed 24 Hours

ID: Q190633

The information in this article applies to:

Microsoft Excel 98 Macintosh Edition

SUMMARY

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.

MORE INFORMATION

To add times with a sum that exceeds 24 hours, use 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: 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 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. Follow these steps to see the expected total of "38:30":

Select the cell that contains 14:30.

On the Format menu, click Cells, and then click the Number tab.

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.

REFERENCES

For more information about time formats, click Contents And Index on the Help menu (or on the Balloon Help menu if you are using a version of the Macintosh operating system earlier than 8.0), click the Index button in Microsoft Excel Help, type the following text

time formats

and then click Show Topics. Select the "Custom number, date, and time format codes" topic, and click Go To. If you are unable to find the information you need, ask the Office Assistant.

Additional query words: XL98 accumulate

Keywords : kbdta xlformula
Version : MACINTOSH:98
Platform : MACINTOSH
Issue type : kbhowto