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

We have 10 time values in the “Hour:Minute:Second” format in column B. We want to convert them to decimals.

Method 1 – Using Arithmetic Multiplication

Steps:

• Click on the C5 cell and insert the following formula.

• Press the Enter button.

• Place your cursor on the bottom-right corner of the C5 cell.
• A black fill handle will appear. Drag it down.

• You will get all the hour decimal values for the time values.

• To get minute values, click on the D5 cell and insert the following formula.

• Hit the Enter button.

• Use the Fill handle feature to copy the same formula for all the other cells below.
• To convert to seconds, click on the E5 cell and insert the following formula in the formula bar.

• Hit the Enter button.

• Use the fill handle feature to copy the same formula for all the other cells below.

Method 2 – Using the CONVERT Function

Steps:

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

• Hit the Enter button.

• Drag down the Fill Handle (bottom-right corner) from the first result cell.

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

• Press the Enter button.

• Use the Fill handle feature to copy the same formula for all the other cells below.
• To convert the time values into seconds values, click on the E5 cell and insert the formula below.

• Hit the Enter button.

• Use the Fill handle feature to get all the other seconds values from the time values.

Method 3 – Combining the HOUR, MINUTE, and SECOND Functions to Convert Time to Decimal

Steps:

• Click on the C5 cell and insert the following formula to convert time to a decimal hours value.

• Hit the Enter button.

• Drag down the Fill Handle (bottom-right corner of the cell) to AutoFill the column.

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

• Hit the Enter button.

• Use the Fill handle feature to convert all the other time values to the minutes values.
• To convert time values to seconds values, click on the E5 cell and insert the following formula.

• Press the Enter button.

• Use the fill handle feature to get all the other seconds values from the respective time values.

Convert Decimal to Time Over 24 Hours

We have a list of decimal hours above 24 in column B.

Steps:

• Click on the C5 cell and insert the formula below.

• Hit the Enter button.

• Drag down the Fill Handle.

You will get all the decimal values converted to time values over 24 hours.

Things to Remember

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

• Keep the time cell values in the h:mm:ss format.

• You can format the cells by pressing Ctrl + 1 when selecting cells.

Download the Practice Workbook

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