How to display hours over 24

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 into Time format. Excel displays 12H. This is easy to understand because 0.5 is half of 1. And 1 is one day in Excel so 0.5 of 24 hours is 12H 😉

Hour in Time format vs Standard format

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

=your value/24

The second solution is to use the paste special tool, division option, to convert our whole number 😀😍👍

Copy Paste-Special option Divide

Have a look at the tutorial video to understand the technique to use the Divide option:

Why Excel doesn't display over 24 hours ?

In this example, all the values are in hour format. The SUM of all the cells returns a value greater than 1 but the format is not yet in time but in standard format (it's better to understand)

Result of the sum of time in standard format

But now, if you convert this result into Time format, you don't see the result with values over 24 hours.

By default, Excel doesn't display hours over 24

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.

  • By using the shortcut Ctrl+1
  • Or, by selecting the last menu in the dropdown Number Format list
Menu to open the custom format

In the dialog box Format Cells, write the following code with the hour code between brackets.


Customize your time format to display hours over 24
  • The [h] between bracket means the hours over 24 hours
  • The mm means the minute format
Excel displays time over 24H

Related posts

Permanent link to this article:

Leave a Reply

Your email address will not be published.