Md. Abdul Kader
When you have to do a further calculation using the time in Excel, you need to convert the time into decimals. Because you may get the time in specified formatting e.g. hh:mm:ss by default. In this guiding session, I’ll show you 4 cases for converting time to decimals in Excel.
Download Practice Workbook
When Do You Need Converting Time to Decimals?
Let’s try to understand the significance of converting time to decimals in Excel with a real-life example.
Assuming that you have an Employee Timesheet where the Starting Time, Ending Time, Working Time (the subtraction of Starting Time and Ending Time), and Hourly Rate of payment are provided.
Now, you need to calculate the payment for each employee based on the corresponding working hours.
Simply, you can multiply the Hourly Rate by Working Time to get the Payment as shown in the below screenshot. So, your used formula might be like the following one.
=F5*E5
Here, F5 is starting cell of Hourly Rate, and E5 is starting cell of Working Time. Unfortunately, you’ll get the incorrect output as Working Time which is in h: mm format. So, you need to convert the time into decimals.
Note: Here, the Working Time is in h: mm format. You can specify the format using the Format Cells option (the keyboard shortcut is Ctrl + 1).
4 Cases of Converting Time to Decimals in Excel
In this section, you’re going to explore 4 cases. The first one deals with simple multiplication. The next two examples show the use of the Excel functions. And, the rest case is suitable if you have the date and time together in a single cell.
1. Applying Simple Multiplication
1.1. Converting Time to Decimal Hours
If you multiply the time value by the number of hours in a day i.e 24, you’ll get the time as hours. So, the formula for the D5 cell will be as follows.
=C5*24
Now, multiply the Hourly Rate by the Working Hours (Decimals) to get the Payment.
1.2. Converting Time to Decimals Minutes
Similarly, you need to multiply the number of minutes i.e. 24*60 or 1440 with the Working Time to convert the time into minutes.
=C5*24*60
1.3. Converting Time to Decimals Seconds
Again, multiply the number of seconds in a day i.e. 24*60*60 or 3600 with the Working Time to find the time as seconds.
=C5*24*60*60
Read More: How to Convert Hours to Decimal in Excel (3 Easy Methods)
2. Using HOUR, MINUTE, and SECOND Functions
Besides, you can use some useful Excel functions e.g. HOUR, MINUTE, and SECOND to convert time to decimals easily. The HOUR function returns the hour as a number from 0 to 23. Moreover, the MINUTE function returns the number of minutes from a given time value. And the SECOND function Returns the second, a number from 0 to 59.
2.1. Converting Time to Decimals Hours
If you want to convert time to hours (decimals), you’ll need to use the following formula.
=HOUR(C5)+MINUTE(C5)/60+SECOND(C5)/3600
Here, you have to divide the output of the MINUTE function by the number of minutes in an hour i.e. 60, and the output of the SECOND function by the number of seconds in an hour i.e. 3600.
2.2. Converting Time to Decimals Minutes
Similarly, you have to multiply hours by the number of minutes in an hour and divide seconds by the number of seconds in a minute. So, the formula will be like the following.
=HOUR(C5)*60+MINUTE(C5)+SECOND(C5)/60
Read More: How to Convert Minutes to Decimal in Excel (3 Quick Ways)
2.3. Converting Time to Decimals Seconds
Again, you need to multiply hours by the number of seconds in an hour and minutes by the number of seconds in a minute. So, the adjusted formula will be-
=HOUR(C5)*3600+MINUTE(C5)*60+SECOND(C5)
Read More: How to Convert Decimal to Minutes and Seconds in Excel (3 Easy Ways)
Similar Readings
- How to Fix Decimal Places in Excel (7 Simple Ways)
- Insert Dot between Numbers in Excel (3 Ways)
- How to Remove Decimal Places in Excel (5 Easy Methods)
- Reduce Decimals in Excel (10 Easy Methods)
3. Utilizing CONVERT Function
Luckily, there is another Excel function i.e CONVERT function which is a widely used function for converting the measure from one unit to another unit. Certainly, you can utilize this function to convert time to decimals if you find it necessary.
3.1. Converting Time to Decimals Hours
The CONVERT function has 3 arguments. The first one is the number argument where you have to choose the C5 cell. In the from_unit argument, you need to select “day” from a lot of options. Lastly, pick the “hr” as the to_unit argument to convert the time to hours (decimals). Therefore, the formula for the D5 cell will be as follows.
=CONVERT(C5,"day","hr")
3.2. Converting Time to Decimals Minutes
In the case of converting time to minutes (decimals), you have to choose the “mn” as the last argument.
=CONVERT(C5,"day","mn")
3.3. Converting Time to Decimals Seconds
Furthermore, the formula will be like the following if you want to convert time to seconds (decimals).
=CONVERT(C5,"day","sec")
Read More: How to Convert Decimal to Days Hours and Minutes in Excel (3 Methods)
4. Splitting Date-Time and Converting Time to Decimals
Lastly, I’m showing you another example where the date and time are provided in a single cell. So, if you want to convert the time to decimals, you have to split the time from the date-time combination first. In such a case, you can apply the MOD function.
=MOD(B5,1)
Alternatively, you can use the INT function too. In that case, the formula will be-
=B5-INT(B5)
Now, you may apply any method from the previously discussed 3 ways of converting time to decimals.
For example, the formula for converting the split time to hours (decimals) will be-
=C5*24
Similarly, the formula in the case of converting the time to minutes will be-
=C5*24*60
And, the formula for converting time to seconds will be-
=C5*24*60*60
Read More: How to Convert Hours and Minutes to Decimal in Excel (2 Cases)
Conclusion
That’s all about today’s session. This is how you can use the methods for converting time to decimals efficiently. I firmly believe this session would be highly beneficial for you. However, don’t forget to share your thoughts in the comments section below.
Related Articles
- How to Set Decimal Places in Excel with Formula (5 Effective Ways)
- Excel 2 Decimal Places without Rounding (4 Efficient Ways)
- How to Remove Decimals in Excel with Rounding (10 Easy Methods)
- Remove Decimals Without Rounding in Excel (4 Suitable Ways)
- How to Change Decimals to Percentages in Excel (4 Easy Ways)
