Explanation
In the Excel date system, one day is equal to 1, so you can think of time as fractional values of 1, as shown in the table below:
| Hours | Fraction | Value | Time |
|---|---|---|---|
| 1 | 1/24 | 0.04167 | 1:00 |
| 3 | 3/24 | 0.125 | 3:00 |
| 6 | 6/24 | 0.25 | 6:00 |
| 4 | 4/24 | 0.167 | 4:00 |
| 8 | 8/24 | 0.333 | 8:00 |
| 12 | 12/24 | 0.5 | 12:00 |
| 18 | 18/24 | 0.75 | 18:00 |
| 21 | 21/24 | 0.875 | 21:00 |
This means if you have a decimal number for hours, you can simply divide by 24 to get the correct representation of hours in Excel. After dividing by 24, you can apply a time format of your choice, or use the result in a math operation with other dates or times.
In the example, since B10 contains 12 (representing 12 hours) the result is 12/24 = 0.5, since there are 12 hours in a half of day. Once a time format like h:mm has been applied, Excel will display 12:00.
Durations longer than 24 hours
To display hours that represent a duration longer than 24 hours, you’ll need to adjust the number format. Just wrap the h in square brackets like so:
[h]:mm
To display in minutes, you can do the same thing with m:
[m]
The brackets tell Excel the time is a duration, and not a time of day. This article explains number formats in more detail.
Explanation
In the Excel date system, one day is equal to 1, so you can think of time as fractional values of 1, as shown in the table below:
| Hours | Fraction | Minutes | Value | Time |
|---|---|---|---|---|
| 1 | 1/24 | 60 | 0.04167 | 1:00 |
| 3 | 3/24 | 180 | 0.125 | 3:00 |
| 6 | 6/24 | 360 | 0.25 | 6:00 |
| 4 | 4/24 | 240 | 0.167 | 4:00 |
| 8 | 8/24 | 480 | 0.333 | 8:00 |
| 12 | 12/24 | 720 | 0.5 | 12:00 |
| 18 | 18/24 | 1080 | 0.75 | 18:00 |
| 21 | 21/24 | 1260 | 0.875 | 21:00 |
This means if you have a decimal number for minutes, you can divide by 1440 (24 x 60) to get the correct representation of minutes in Excel. After dividing by 1440, you can apply a time format of your choice, or use the result in a math operation with other dates or times.
In the example, since B11 contains 720 (representing 720 minutes) the result is 720/1440 = 0.5. Once a time format like h:mm has been applied, Excel will display 12:00.
Durations
To display hours that represent a duration longer than 24 hours, or minutes in durations longer than 60 minutes, you’ll need to adjust the number format by adding square brackets.
[h]:mm // hours > 24
[m] // minutes > 60
The brackets signal to Excel that the time is a duration, and not a time of day.
