How to display hours over 24 in Excel?

How to display hours over 24 in Excel?
Last Updated on 02/12/2023
Reading time: 3 minutes

How to display hours over 24 H in Excel?

  1. Why Excel doesn't display hours beyond 24 hours

    By default, the maximum hours is 23:59:59 in Excel

  2. Customize your time format

    To display hours over 24 hours, you must create your own time format

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.
Hour in Time format vs Standard format

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.

Result of the sum of time in standard format

But now, if you convert this result into Time format, all the cells show a result under 24 hours.

By default, Excel doesn't display hours over 24

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 💡

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 dialogue box Format Cells, write the following code with the hour code between brackets.

[h]:mm

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

Trick to Convert whole numbers to decimal numbers

If your document represents hours but in reality it's integer, like 8 for 8 hours, there is an easy way to convert all your data.

  1. Write the value 24 in an empty cell
  2. Copy it
  3. Select all the data to convert
  4. Open the dialog box Paste Special.
  5. Select the option Divide
Copy Paste-Special option Divide

This video explains how to proceed.

Related Articles

6 Comments

  1. Rob
    22/09/2023 @ 20:40

    Ok..... worked it out.
    Thanks

    Reply

  2. Rob
    22/09/2023 @ 10:26

    Hi.
    I have a cell containing a total time as 38:15 (formatted as [h]:mm). I now need to show that value as 38 Hrs 15 Min. When I use the formula =HOUR($H$33)&" Hrs "&MINUTE($H$33)&" Min", I get 14 Hrs 15 Min. The calculated cell is also formatted as [h]:mm. Anyone know how to display this correctly?
    Thanks
    Rob

    Reply

  3. Chris
    02/02/2023 @ 04:43

    you're my new BFF

    Reply

  4. Hans Hallebeek
    04/11/2021 @ 08:52

    Nice, but how would I go to display 123 hours and 45 minutes? [hhh]:mm ????

    Reply

    • Frédéric LE GUEN
      07/11/2021 @ 15:08

      No, [h]:mm is enough if the value of the hours is 123

      Reply

  5. Clauds
    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*

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

How to display hours over 24 in Excel?

Reading time: 3 minutes
Last Updated on 02/12/2023

How to display hours over 24 H in Excel?

  1. Why Excel doesn't display hours beyond 24 hours

    By default, the maximum hours is 23:59:59 in Excel

  2. Customize your time format

    To display hours over 24 hours, you must create your own time format

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.
Hour in Time format vs Standard format

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.

Result of the sum of time in standard format

But now, if you convert this result into Time format, all the cells show a result under 24 hours.

By default, Excel doesn't display hours over 24

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 💡

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 dialogue box Format Cells, write the following code with the hour code between brackets.

[h]:mm

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

Trick to Convert whole numbers to decimal numbers

If your document represents hours but in reality it's integer, like 8 for 8 hours, there is an easy way to convert all your data.

  1. Write the value 24 in an empty cell
  2. Copy it
  3. Select all the data to convert
  4. Open the dialog box Paste Special.
  5. Select the option Divide
Copy Paste-Special option Divide

This video explains how to proceed.

Related Articles

6 Comments

  1. Rob
    22/09/2023 @ 20:40

    Ok..... worked it out.
    Thanks

    Reply

  2. Rob
    22/09/2023 @ 10:26

    Hi.
    I have a cell containing a total time as 38:15 (formatted as [h]:mm). I now need to show that value as 38 Hrs 15 Min. When I use the formula =HOUR($H$33)&" Hrs "&MINUTE($H$33)&" Min", I get 14 Hrs 15 Min. The calculated cell is also formatted as [h]:mm. Anyone know how to display this correctly?
    Thanks
    Rob

    Reply

  3. Chris
    02/02/2023 @ 04:43

    you're my new BFF

    Reply

  4. Hans Hallebeek
    04/11/2021 @ 08:52

    Nice, but how would I go to display 123 hours and 45 minutes? [hhh]:mm ????

    Reply

    • Frédéric LE GUEN
      07/11/2021 @ 15:08

      No, [h]:mm is enough if the value of the hours is 123

      Reply

  5. Clauds
    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*

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *