Excel doesn't display hours over 24 by default.
But, if you change one option in the number format, you can display hours over 24.
Difference between hours and days in Excel
Rule 1 : A whole number is a day
Rule 2 : A decimal number, between 0 and 1, is a time
Let's consider the value 0.5, and convert it in Time format. Excel displays 12H
This is easy to understand because 0.5 is half of 1. So because 1 is 1 day, 24 hours, 0.5 is 12H 😉
Convert whole number to decimal number
If you have created an Excel worksheet with whole number that represent hours, you MUST convert them.
The first solution is to apply this formula
The second solution is to use the paste special tool, option division, to convert your whole number 😀😍👍
Have a look at the video for the tutorial
Why Excel doesn't display over 24 hours ?
In this example, all the values are in hour format (in decimal number).
The SUM of all the cells returns a value greater than 1 (General format).
But now, if you convert this result in Time format, you don't see the result with values over 24 hours.
The reason it's because after 23:59:59, it's 1 day. So Excel converts the result in days for the whole number and hours for the rest.
Display hours over 24
To display hours over 24 you must customize your number format with brackets.
Open the custom number format
- With the shortcut Ctrl+1
- Or by selecting the last menu in the dropdown Number Format list
- Select the tab Number
- Choice the menu Custom
- Insert the following code
- The h between bracket means the hours over 24 hours
- The mm means the minute format