Free Practice WorkbookTable of Contents
Understanding Decimal Time
Before looking into the Excel functions, it’s important to understand the concept of decimal time. In traditional time notation, an hour is divided into 60 minutes, and a minute is divided into 60 seconds. However, in decimal time, an hour is represented as 1, and fractions of an hour are expressed in decimals. For example, 30 minutes is represented as 0.5 (since 30 minutes is half of an hour).
Let’s explore the process of determining the hours worked by employees:
How to Convert Decimal to Time
Method 1
To convert these decimals to minutes (hh:mm):
- Go to cell C2
- Enter =B2/24
Since there are 24 hours in a day
- Drag the small box located at the bottom-right corner of cell C2 downward to automatically extend and apply the formula to the remaining hours.
- Right-click the selected cells and go to Format Cells.
- Go to Custom.
- Delete the General and input h:mm.
- Click OK.
Method 2
You can use the TEXT function to convert a decimal to time as text.
=TEXT(A2/24,”h:mm”)
Sum the Hours
To sum the hours worked, go to D2, and enter =SUM(C2:C7).
Right-click on D2, and click on Format Cells.
Go to Custom. Put brackets around the letter [h]:mm.
Click OK.
Tips & Tricks
- Always divide decimal hours by 24 before formatting as time.
- Decimal days only need formatting and no conversion.
- Use TEXT() when you need a text representation instead of a time value.
- Apply [h]:mm to display durations longer than 24 hours.
- Use ROUND() to avoid unexpected seconds caused by decimal precision.
FAQs
Why divide decimal hours by 24 in Excel?
Excel stores one day as 1, so dividing by 24 converts hours into Excel time.
How to display decimal hours as time?
To display decimal hours as time,
- Divide the decimal hour by 24
- Right-click and select Format Cells.
- Apply a custom time format like h:mm
When to use the TEXT function?
You can use the TEXT function when you want the time displayed as text instead of a time value.
How to total hours greater than 24?
If the total hours are greater than 24, you should apply the custom format [h]:mm.
Why do I see extra seconds after converting time?
Decimal precision can create extra seconds, so use the ROUND() function to remove them.
John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship MyExcelOnline Academy Online Course.
30 Days - Full Access 
