Converting Time to Decimals in Excel (4 Examples)

Get FREE Advanced Excel Exercises with Solutions!

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.

Dataset

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.

Incorrect Calculation

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

Converting Time to Decimals Hours in Excel Applying Simple Multiplication

Now, multiply the Hourly Rate by the Working Hours (Decimals) to get the Payment.

Applying Simple Multiplication


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

Converting Time to Decimals Minutes in Excel Applying Simple Multiplication


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

Applying Simple Multiplication

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.

Converting Time to Decimals Hours in Excel Using HOURS, MINUTE & SECOND Functions


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

Using HOURS, MINUTE & SECOND Functions

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)

Using HOURS, MINUTE & SECOND Functions

Read More: How to Convert Decimal to Minutes and Seconds in Excel (3 Easy Ways)


Similar Readings


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")

Converting Time to Decimals Hours in Excel Utilizing CONVERT Function


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")

Utilizing CONVERT Function


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")

Converting Time to Decimals Seconds in Excel Utilizing CONVERT Function

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)

Splitting Date-Time

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

Converting Time to Hours After Splitting Date-Time

Similarly, the formula in the case of converting the time to minutes will be-

=C5*24*60

Split Date-Time and Convert

And, the formula for converting time to seconds will be-

=C5*24*60*60

Split Date-Time and Convert

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

Md. Abdul Kader
Md. Abdul Kader

Hi! I am Abdul Kader and presently working as ‘Excel & VBA Content Developer’ at Exceldemy. I publish my articles related to Microsoft Excel here. In 2019, I completed my graduation in Urban and Regional Planning from Chittagong University of Engineering and Technology. Having extreme eagerness to learn, I want to develop my skills and capabilities higher.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo