XL: Maximum Times in Microsoft Excel

PSS ID Number: Q168435

Article Last Modified on 09-4-2001

• Microsoft Excel 97 for Windows
• Microsoft Excel for Windows 95 7.0, 7.0a
• Microsoft Excel for Windows 5.0, 5.0c

Summary

In Microsoft Excel, there are limits to the maximum times that you can type into a cell or return by using a formula. This article explains the limits in different versions of Microsoft Excel.

More Information

In Microsoft Excel, versions 5.0 and later, you can enter times that exceed 24 hours by using a number format that includes "[h]" (without the quotation marks). The square brackets around the "h" instruct Microsoft Excel calculate times beyond 24-hour intervals, which allows you to enter times that exceed 24 hours. If you enter a time that exceeds 24 hours, Microsoft Excel automatically applies a number format that includes the "[h]" code.

There are limits that apply to times that use this format. If you type a time value into a cell or use a formula to return a time that exceeds the limits, you may notice either of the following behaviors:

• If you enter a time that exceeds the limits, the time may appear as a text string.
  
  For example, if you type the following time in Microsoft Excel 97

  20000:00:00

  the time appears in the cell as a text string aligned to the left.



• If you use a formula to return a time that exceeds the limits, the formula returns a series of pound or number signs (####).
  
  For example, if you use the following values and formulas in a worksheet in Microsoft Excel 5.0

  A1: 12/31/2078 23:00:00
  A2: 1:00:00
  A3: =A1+A2
  

  the formula returns a series of pound signs because the time returned by the formula exceeds the maximum time for Microsoft Excel 5.0.

Microsoft Excel for Windows, version 5.0

Microsoft Excel for Windows, versions 5.0c, 7.0, 7.0a

Microsoft Excel 97 for Windows

References

For more information about using times in Microsoft Excel, please see the following articles in the Microsoft Knowledge Base: