Let’s take the example below:
| A | B | C | ||
|---|---|---|---|---|
| 1 | Lessons | NumberOfMinutesPerLesson | TotalDurationDecimal | TotalDurationFormated |
| 2 | 50 | 5 | 4.16 | 4:10:00 |
| 3 | 35 | 6 | 3.50 | 3:30:00 |
The caveat
In Google Sheet, the conversion from a decimal to a duration value follow some rules.
For example,
1is equivalent to24:00:001.35is equivalent to32:24:000.5is equivalent to12:00:000.05125is equivalent to01:13:48
So if we format the decimal values above, the result won’t be the 4:10:00 and 3:30:00 expected.
The solution
Thankfully, it’s easy to solve that.
Given that we have applied the duration formatting on the column D, the formula is as follows:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Lessons | NumberOfMinutesPerLesson | TotalDurationDecimal | TotalDurationFormated |
| 2 | 50 | 5 | =A2*B2 | =C2/60/24 |
| 3 | 32 | 6 | =A3*B3 | =C3/60/24 |
Column C contains a number of hours.
Since the formatting will apply 1 = 24h , we simply need to divide the column C value by 60 and then by 24.
When the duration formatting is applied, it’ll therefore display the proper duration.
Follow me
Thanks for reading this article. Make sure to follow me on X, subscribe to my Substack publication and bookmark my blog to read more in the future.
Photo by FOX ^.ᆽ.^= ∫.
