Download Excel Workbook
Watch our free training video on How to Convert Decimals to Time in Excel:
Imagine you are given task to convert decimals to time in Excel format for accurate payroll calculations. In this scenario, you receive time entries from employees in decimal minutes format, where each decimal represents the fraction of an hour spent working.
In this article, we’ll explore the process of converting decimals to time in Excel, providing step-by-step guidance to help you achieve accurate results –
Let us look at each of these steps in detail.
Download the Excel Workbook below to follow along and understand how to turn Decimals to Time in Excel –
download excel workbook DecimalstoTimeinExcel.xlsx
Table 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).
Scenario
Let’s explore the process of determining the hours worked by employees:
Our goal here is to convert these decimals to minutes (hh:mm) to streamline the payroll process. The time format is essential for accurate calculations and better representation of working hours.
We also have a tutorial on how to do the reverse, Convert Time to Decimal!
Solution
Go to cell C2 and enter =B2/24. This formula we are going to use divides the decimal value by 24 (since there are 24 hours in a day) and formats the result as hours and minutes.
As you can see, the formula has converted the time to Excel’s time format.
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.
As you can see the, the decimals have been converted to a more readable time format.
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.
Conclusion
There you have it! Whether you’re working on timesheets, scheduling tasks, or analyzing time-based data, mastering this conversion process will enhance your Excel proficiency.
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 Academy Online Course.
