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
To understand this, have a look at this example
- Let's consider the value 0.5
- Convert it into Time format. Excel displays 12H.
The explanation is simple. 0.5 is half of a day so it's 12H.
Why Excel doesn't display over 24 hours?
In this example, all the values are in Time format.
When you do the sum of the weekdays, the duration exceeds 24 hours. Here is column H, we have the SUM function in General format number.
But now, if you convert this result into Time format, all the cells show a result under 24 hours.
It's because, after 23:59:59, it's 1 day.
So Excel converts the result into days for the whole number and hours for the rest 💡
Trick to Convert whole numbers to decimal numbers
If your document wants to represent hours but in reality it's whole numbers, like 8 for 8 hours, there is an easy way to convert all your data.
- Write the value 24 in an empty cell
- Copy it
- Select all the data to convert
- Open the dialog box Paste Special.
- Select the option Divide
This video explains how to proceed.
Display hours over 24
To display hours over 24, you must customize your number format with brackets.
Open the custom number format.
- By using the shortcut Ctrl+1
- Or, by selecting the last menu in the dropdown Number Format list
In the dialogue box Format Cells, write the following code with the hour code between brackets.
- The [h] between brackets means the hours over 24 hours
- The mm means the minute format
02/02/2023 @ 04:43
you're my new BFF
04/11/2021 @ 08:52
Nice, but how would I go to display 123 hours and 45 minutes? [hhh]:mm ????
Frédéric LE GUEN
07/11/2021 @ 15:08
No, [h]:mm is enough if the value of the hours is 123
12/11/2020 @ 22:35
Thank you SO much. Made it so simple and you didn't even try to sell me Ku Tools once. As of today this is the only site I found explaining how to sum up hours from decimal values spanning over months. *Bookmarking now*