Microsoft Office Tutorials and References

In Depth Information

**Displaying Times That Exceed 24 Hours**

Displaying Times That Exceed 24 Hours

There’s nothing really special about a time value. An Excel time value is a normal number that is

formatted to display as a time. For example, 0 represents 12:00 a.m., 0.50 represents noon (half-

way through the day), and 0.75 represents 6:00 p.m.

Because time values are numbers, you can add them together. Figure 58-1 shows a worksheet

that sums several time values. The formula in cell B8 is a simple SUM formula:

=SUM(B3:B7)

Figure 58-1:
Summing time values might not display the correct result.

As you can see, the formula is returning an incorrect result. Because a day has only 24 hours,

Excel normally ignores hours that exceed 24 hours. To force Excel to display times that exceed

24 hours, you modify the number formatting in the Number tab of the Format Cells dialog box.

In this example, Excel uses the following number format:

h:mm

To display the correct value, edit the number format code by placing square brackets around the h:

[h]:mm

Figure 58-2 shows the worksheet after making this number format change.