How to Convert Time into Decimals in Excel

This guide will explain how to convert time into decimals in Excel using two simple methods.

The rules for using the CONVERT function in Excel are the following:

  • The CONVERT function converts a number from one measurement system to another.
  • The CONVERT function is case-sensitive.
  • When a unit string is not recognized, the function will return a #N/A error. 
  • When units are incompatible, the function will return a #N/A error. 
  • When a number is invalid, the function will return a #VALUE! error. 

Excel is a popular tool to use for different situations and purposes. Since it has several built-in functions and tools, we can easily perform complex and long calculations. For instance, there are many ways we can convert time in Excel to any format we want.

In this guide, we want to focus on converting time into decimals, such as the number of hours, minutes, or seconds. Luckily, we can easily perform this using two methods in Excel. So one of the methods is to do the arithmetic way and multiply the values. And the second method is to use the CONVERT function.

Let’s take a sample scenario wherein we need to convert time into decimals in Excel.

Suppose you are creating a time sheet to calculate the salary for each employee. However, you would need the time to be in decimals to easily calculate the number of hours worked by the employee times the rate for you to determine their salary. 

To do this, you simply decided to perform the arithmetic ways and multiply the time by the value of each hour, minute, and second. 

Before we move on to a real example of converting time into decimals in Excel, let’s first learn how to use the CONVERT function.

 

The Anatomy of the CONVERT Function

The syntax or the way we write the CONVERT function is as follows:

=CONVERT(number, from_unit, to_unit)

Let’s take apart this formula and understand what each term means:

  • = the equal sign is how we start any function in Excel.
  • CONVERT() is our CONVERT function. And this function is used to return a number from one measurement system to another. 
  • number is a required argument. So this refers to the value we want to convert to another measurement system.
  • from_unit is also a required argument. And this refers to the original unit of the number we want to convert.
  • to_unit is another required argument. So this refers to the unit we want the number to be converted into or the unit for the result. 

Great! Now we can dive into a real example of converting time into decimals in Excel.

 

A Real Example of Converting Time into Decimals in Excel

Let’s say we have a data set containing the total time an employee has worked for. And we want to convert the time into decimals of hours, minutes, and seconds to easily calculate each employee’s salary. So our initial data set would look like this:

Initial data set

 

Fortunately, there are two methods we can utilize to convert time into decimals in Excel. Firstly, we can easily convert time into decimals using the traditional arithmetic way of multiplication. So we simply multiply the time by the number of hours, minutes, or seconds in a day to convert it into a decimal.

To convert time into the decimal for hours, we simply multiply by 24, which is the number of hours in a day. To convert time into the decimal for minutes, we simply multiply by 24*60 or 1440, which is the number of minutes in a day. 

Lastly, we simply multiply the time by 26*60*60 or 86400, the number of seconds in a day to convert time into the decimal for seconds.

Secondly, we can utilize the CONVERT function to change the time into decimals. So the CONVERT function is used to convert a number from one measurement system to another measurement system. To do this, we will simply select the time we want to convert. 

Then, we will input the original measurement system, which is “day”. Lastly, we will input the measurement system we want the time to be converted. For instance, we want to convert time into the decimal for hours. So we will input hour as our argument in the formula.

Before we use any of the two methods in our data set, we first need to ensure the cells containing the time are in the proper format. To do this, we will simply go to the format cells dialogue box and choose the time format that follows the format hr:min:sec. 

Afterward, we can apply any of the two methods to convert time into a decimal for hours, minutes, or seconds. So our final data set would look like this:

Final data set

 

You can make your own copy of the spreadsheet above using the link attached below. 

Amazing! Now we can move on and proceed to explain the steps of how to convert time into decimals using two simple and easy methods.

 

How to Convert Time into Decimals in Excel

In this section, we will explain the step-by-step process of how to convert time into decimals in Excel using two easy and simple methods. 

1. Firstly, we need to ensure the cells containing the time are in the proper format. To do this, we can simply select the column and right-click. Then, we will click Number Format.

Proper time format

 

2. In the Number Format window, we will choose the Time category. Next, we will select the format that follows the hr:min:sec without the AM or PM label. Lastly, we will click OK to apply the changes.

Time format

 

3. Now we can try our first method, the arithmetic method. Before we begin, let’s first create columns for hours, minutes, and seconds where we will input the results. Firstly, let’s convert time into hours.

To do this, we will simply multiply the time by 24. So type in the formula “=C2*24”. Lastly, we will press the Enter key to return the result.

Convert Time into Decimals in Excel

 

4. Next, we will convert the time into minutes by multiplying time by 1440. So type in the formula “=C2*1440”. Lastly, we will press the Enter key to return the result.

Convert Time into Decimals in Excel

 

5. Then, we will convert the time into seconds by multiplying time by 86400. So we will input the formula “=C2*86400”. Lastly, we will press the Enter key to return the result.

Convert Time into Decimals in Excel

 

6. Afterward, we can simply drag down the Fill Handle tool for each column to copy and apply the formula to the other cells.

Convert Time into Decimals in Excel

 

7. Now let’s try using the CONVERT function. To convert time into decimal, we will simply type in the formula “=CONVERT(C2, “day”, “hr”)”. Lastly, we will press the Enter key to return the result.

Convert Time into Decimals in Excel

 

 

8. Next, we will convert time into minutes by typing in the formula “=CONVERT(C2, “day”, “min”)”. Finally, we will press the Enter key to return the result.

Convert Time into Decimals in Excel

 

9. Then, we will convert the time into seconds using the formula “=CONVERT(C2, “day”, “sec”)”. Lastly, we will press the Enter key to return the result.

Convert Time into Decimals in Excel

 

10. Finally, we will drag down the Fill Handle tool to copy the formula to the other cells.

Convert Time into Decimals in Excel

 

11. And tada! We have successfully converted time into decimals in Excel.

Convert Time into Decimals in Excel

 

And that’s pretty much it! We have explained how to convert time into decimals in Excel using two simple methods. Now you can simply choose any method and apply it to your work whenever you need it. 

Are you interested in learning more about what Excel can do? You can now use the CONVERT function and the various other Microsoft Excel formulas available to create great worksheets that work for you. Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'll love what we are working on! Readers receive ✨ early access ✨ to new content.

0 Shares:
Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like