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.


Download Practice Workbook

You can download our practice workbook from here for free!


3 Quick Ways to Convert Time to Decimal in Excel

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.

Sample Dataset to Convert Time to Decimal in Excel


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.
=B5*24
  • Subsequently, press the Enter button.

Arithmetic Formula to Convert Time to Decimal Hours Value

  • 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.

Drag the Fill Handle to Copy the Same Formula

  • Thus, you will get all the hour decimal values for the time values.
=B5*1440
  • Following, hit the Enter button.

Arithmetic Formula to Convert Time to Decimal Minute Value

  • 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.
=B5*86400
  • Subsequently, hit the Enter button.

Arithmetic Formula to Convert Time to Decimal Seconds Value in Excel

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.

Converted Time to Decimal in Excel

Read More: How to Convert Hours to Minutes in Excel (3 Easy Ways)


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.
=CONVERT(B5,"day","hr")
  • Following, hit the Enter button.

Using the CONVERT Function to Convert Time to Decimal Hours Value

  • 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.

Drag Fill Handle Below

  • Now, to convert the time values to minutes values, click on the D5 cell and insert the formula below.
=CONVERT(B5,"day","mn")
  • Afterward, press the Enter button.

Using CONVERT Function to Convert Time to Decimal Minutes Value in Excel

  • 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.
=CONVERT(B5,"day","sec")
  • Following, hit the Enter button.

Using CONVERT Function to Convert Time to Decimal Seconds Value in Excel

  • 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.

Converted Time to Decimal in Excel

Read More: How to Convert Minutes to Seconds in Excel (2 Quick Ways)


Similar Readings


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.
=HOUR(B5)+MINUTE(B5)/60+SECOND(B5)/3600
  • Subsequently, hit the Enter button.

Combine HOUR, MINUTE, and SECOND Functions to Convert Time to Decimal Hours in Excel

  • 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.

Use the Fill Handle Feature to Copy the Same Formula

  • 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.
=HOUR(B5)*60+MINUTE(B5)+SECOND(B5)/60
  • Subsequently, hit the Enter button.

Combine Time Functions to Convert Time to Decimal Minutes in Excel

  • 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.
=HOUR(B5)*3600+MINUTE(B5)*60+SECOND(B5)
  • Afterward, press the Enter button.

Combine the Time Functions to Convert Time to Decimal Seconds in Excel

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.

Converted Time to Decimal in Excel

Read More: Convert Time to Minutes in Excel (5 Suitable Methods)


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.
=TEXT(B5/24,"[h]:mm")
  • Subsequently, hit the Enter button.

Using TEXT Function to Convert Decimal to Time over 24 Hours

  • 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.

Use Fill Handle Feature to Copy Same Formula

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

Converted Decimal to Time Over 24 Hours

Read More: Convert Decimal Coordinates to Degrees Minutes Seconds in Excel


💬 Things to Remember

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

Number Format Instructions

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

Time Format Instructions

  • 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.

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.

And, visit ExcelDemy to learn more things about Excel. Have a nice day!


Related Articles

Tanjim Reza

Tanjim Reza

Hello! I am Md. Tanjim Reza Tanim. I have just completed my B.Sc from Naval Architecture & Marine Engineering Department, BUET. Currently, I am working as an Excel & VBA content developer. I always had a great fascination with Microsoft Excel and its cool functions and formulas. Here, I am learning every day about new functions and formulas and working on applying MS Excel to the analysis of our real-life problems. I have great enthusiasm for learning any kind of new things, writing articles, and solving real-life problems.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo