Examples
The TIME function can interpret units in larger increments. For example, both of the formulas below return a result of 2 hours: However, when total time reaches 24 hours, the TIME function will “reset” to zero. In this way, TIME behaves like a 24 hour clock that resets when it crosses midnight. Notably, TIME will not handle numeric inputs larger 32,767. For example, even though there are 86,400 seconds in a day, the following formula (which represents 12 hours) will fail with a #NUM! error: As a workaround, you can convert hours, minutes, and seconds directly to Excel time with a formula: The result is the same as the TIME function up to 24 hours. Over 24 hours, this formula will continue to accumulate time, unlike the TIME function.
Notes
When total time reaches 24 hours, the TIME function will “reset” to zero. The largest number that TIME will allow for hour, minute, or second is 32,767. Larger values will return a #NUM! error.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
