# Custom Formatting and Calculation for Time in Excel

## Time values exceeding 24 hours

The serial value for time, as explained at the beginning
of the chapter, is between 0 and 1.

The time format for a full 24-hour day is hh:mm:ss.

Example: the time 14 hours and 56 minutes is displayed as 14:56:00.

Problem

The default time format does not allow a time value to exceed 24 hours. In a cell, type a value larger than 24 hours, such as 28:56:00. The result is 04:56:00 (the number minus 24 hours).

Solution

Change the format of the cell, and place brackets around the hour. The format is displayed as [hh]:mm:ss, and the result is displayed as  28:56:00

###### Time format table

 H Hour. The number of hours, without 0 if the number islowerthan10. hh Hour. The number of hours, including 0 if the number islowerthan10. M Minute.  The number of minutes, without 0 if the number is lower than10. mm Minute. The number of minutes, including 0 if the number is lower than10. S Second.  One digit per second, without 0 if the number of seconds is lower than10. SS Second. The number of seconds, including 0 if the number is lower than10. [. ] Brackets around the hour, to display a time value that exceeds 24hours

###### Converting hours to decimals

Use the formulas HOUR and MINUTE to convert a time value to a decimal.