How to Convert Time to Decimal in Excel (3 Quick Ways)

Excel is of great use when it comes to thinking about time calculations. At times, you get your dataset in 24-hour time format, but you need the data in decimal values. Regarding this, you will need to convert those time values into decimals. in this article, I will show you 3 quick and suitable ways to convert time to decimal in Excel.

How to Convert Time to Decimal in Excel: 3 Quick Ways

Say, you have 10-time values in Hour: Minute: Second format. Now, you want to convert them to decimals. You can follow any of the 3 suitable ways given below to accomplish this.

1. Using the Arithmetic Multiplication

The simplest way to convert time to decimal in Excel is to use the arithmetic multiplication of Excel. Follow the steps below to accomplish this.

📌 Steps:

• First and foremost, click on the C5 cell and insert the following formula.

• Subsequently, press the Enter button.

• As a result, you will get the decimal value in the hour value for the respective time value.

• Now, if you want to do the same for all the other cells below, place your cursor on the bottom right position of the C5 cell.
• Subsequently, a black fill handle will appear. Drag it downward.

• Thus, you will get all the hour decimal values for the time values.

• Now, you might also need to convert the time values into minute values.
• To do this, click on the D5 cell and insert the following formula.

• Following, hit the Enter button.

• As a result, you will get the minute values for the respective time values and use the Fill handle feature to copy the same formula for all the other cells below.

• Similarly, you can also convert your time values into second values.
• In order to do this, click on the E5 cell and write the following formula in the formula bar.

• Subsequently, hit the Enter button.

Afterward, use the fill handle feature to copy the same formula for all the other cells below.

So, finally, your output should look like this.

2. Using the CONVERT Function

Besides, you can also use the CONVERT function to convert time to hours, minutes, or seconds decimal values. To do this, go through the steps below.

📌 Steps:

• First, to convert the time values into hour decimals, click on the C5 cell and insert the following formula.

• Following, hit the Enter button.

• As a result, you will get the decimal hour value for the respective time value.
• Now, place your cursor in the bottom right position of the cell and a black fill handle will appear consequently.
• Subsequently, drag it down to copy the same formula to get all the hours values for other time values.

• Now, to convert the time values to minutes values, click on the D5 cell and insert the formula below.

• Afterward, press the Enter button.

• Now, you can use the Fill handle feature to copy the same formula for all the other cells below.
• Last but not least, to convert the time values into seconds values, click on the E5 cell and write the formula below.

• Following, hit the Enter button.

• Thus, you will get the seconds decimal value for the respective time value in Excel.

Just like before, you can use the Fill handle feature to get all the other seconds values from the time values. And, at last, the result sheet should look like this.

3. Combining the HOUR, MINUTE & SECOND Functions to Convert Time to Decimal

Another easy way to convert time to decimal values in Excel is to combine the HOUR, MINUTE, and SECOND functions. Follow the steps below to use these functions to get your desired result.

📌 Steps:

• First, click on the C5 cell and insert the following formula to convert time to decimal hours value.

• Subsequently, hit the Enter button.

• Now, for the other cells below, place your cursor in the bottom right position of the C5 cell.
• Consequently, a black fill handle will appear.
• Following, drag it downward.

• Subsequently, you will get all the hour decimal values for all the time values.

• At this time, to get the minute values for the time values, click on the D5 cell and insert the following formula.

• Subsequently, hit the Enter button.

• Now, you can use the Fill handle feature to convert all the other time values to the minutes’ values.
• In addition to these, if you need to convert time values to seconds values, click on the E5 cell and insert the following formula.

• Afterward, press the Enter button.

And, similarly, you can use the fill handle feature to get all the other seconds values from the respective time values. And, at last, the output should look like this.

Convert Decimal to Time Over 24 Hours

Now, you might need to do the opposite sometimes which means converting decimal to time. Say, you are given 10 decimal values over 24 and is needed to convert them to time values. If you want to convert these decimals to time values over 24 hours, you can use the TEXT function to do so. Follow the steps below to accomplish your desired result.

📌 Steps:

• First and foremost, click on the C5 cell and insert the formula below.

• Subsequently, hit the Enter button.

• As a result, you will get the first conversion done.
• Now, place your cursor in the bottom right position of the cell and drag the fill handle below upon its arrival.

Thus, you will get all the decimal values converted to time values over 24 hours. And, for instance, the outcome should look like this.

💬 Things to Remember

• To avoid errors, make sure the decimal-containing cells are in the Number format with two decimal places.

• Another important thing is to keep the time cell values in the h:mm:ss format.

• You can format the cells by pressing Ctrl +1 or clicking the right button of your mouse over cells and choosing the Format Cells… option.

Download Practice Workbook

You can download our practice workbook from here for free!

Conclusion

So, in this article, I have shown you 3 quick ways to convert time to decimal in Excel. I suggest you read the full article carefully and apply the knowledge to your needs. You can also download our free workbook to practice. I hope you find this article helpful and informative. If you have any further queries or recommendations, please feel free to comment here.

Convert Time to Decimal in Excel: Knowledge Hub

• How to Convert Hours to Decimal in Excel
• How to Convert Hours and Minutes to Decimal in Excel
• How to Convert Minutes to Decimal in Excel

<< Go Back to Time Conversion | Date-Time in Excel | Learn Excel