After calculating the time durations in your data, you may now be wondering if there’s a way to view these decimal numbers in time format (h:mm). There sure is.
Excel uses a numeric system (in decimals) to store time values. But we can configure the cell and use formulas to view these decimals in a time format. This way, we can immediately see the number of hours, minutes, and even seconds in one glance.
Convert Decimal to Hours and Minutes by Changing the Number Format
- The first step is to divide the total hours (the one in decimals) by 24.
(24 represents the total hours in a day.)
You can convert the date to the day of the week first for this step to be easier.
On a new column, enter this formula: =[range] / 24
![On a new column, enter this formula: =[range] / 24](https://excelmono.com/wp-content/uploads/2023/05/decimal-to-time-1.png)
If you have a long list of decimals to convert, drag the fill handler down (up to the last row) to apply the same formula.
You will then see the calculated hours in decimals.
2. With all these cells highlighted, go to the Home tab.
Click the small arrow button at the bottom right of the Number section (see the 3rd step in the image below).
Doing so will open the Format Cells menu.
(You can also just press CTRL + 1 if you prefer keyboard shortcuts.)
3. Go to the Number tab. From the list of Categories select Custom.
Inside the Type textbox, enter this format: [h]:mm
- h – serves as the placeholder for the hour.
- mm – serves as the placeholder for the 2-digit minutes.
- the bracket ([]) that wraps around “h” tells Excel to treat the cell value as a time duration and not a time of day.
- Please remember to add this bracket; otherwise, decimals that exceed 24 hours may be incorrectly converted.
If you’re happy with the sample outcome (as shown in the Sample section of the menu), click OK.
4. And that’s it! You now have successfully converted the decimals into hours and minutes.
![Sample output after changing the cell format to [h]:mm](https://excelmono.com/wp-content/uploads/2023/05/decimal-to-time-7.png)
GOOD TO KNOW:
If you want to also view the number of seconds, change the number format to: [h]:mm:ss
IMPORTANT:
If the decimal value that you have represents minutes or seconds (instead of hours), note that you need to use a different formula for that.
Please use the table below as a reference.
| Decimal Values Represented | Formula to Use | Number Format You Can Use |
| Total Hours | = [range] / 24 | [h]:mm |
| Total Minutes | = [range] / (24 * 60) | [h]:mm or [mm]:ss |
| Total Seconds | = [range] / (24 * 60 * 60) | [mm]:ss |
Convert Decimal to Hours and Minutes using the TEXT() Formula
This method is super quick and easy. It doesn’t require you to change the cells’ number format.
However, this approach will convert the cell value into a text format. This means you that can’t use the cells for further calculations. If this doesn’t matter to you, then this method would be a good option.
On a new column, type this formula: =TEXT([range]/24, “[h]:mm”)
![On a new column, type this formula: =TEXT([range]/24, “[h]:mm”)](https://excelmono.com/wp-content/uploads/2023/05/decimal-to-time-8.png)
That’s it! You have already transformed the decimals into hours and minutes format.
Drag the fill handler down to copy the formula to the remaining cells and you’re done.
Like in the previous method, if the decimals represent minutes or seconds (instead of hours), you need to use a different formula.
| Decimal Values Represented | Formula to Use |
| Total Hours | =TEXT([range] / 24, “[h]:mm”) |
| Total Minutes | =TEXT([range] / (24 * 60), “[h]:mm”) or =TEXT([range] / (24 * 60), “[mm]:ss”) |
| Total Seconds | =TEXT([range] / (24 * 60 * 60), “[mm]:ss”) |
Otherwise, remove the time from date if this data is not needed.
You can also check the following articles to learn more how to use decimals in Excel.
Conclusion
As you can see, time durations in decimal format are not that hard to convert into hours, minutes, and seconds in Excel.
For both methods, you need to first identify what the decimal numbers represent – whether they represent total hours, minutes, or seconds.
Once you’ve identified that, you can follow the steps from either of the two methods. Just remember to use the appropriate formula and number format.
Also, you can check our “Convert Decimals to Percentage” tutorial.