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.
=B5*24- 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.
=B5*1440- 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.
=B5*86400- 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.
=CONVERT(B5,"day","hr")- 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.
=CONVERT(B5,"day","mn")- 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.
=CONVERT(B5,"day","sec")- 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.
=HOUR(B5)+MINUTE(B5)/60+SECOND(B5)/3600- 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.
=HOUR(B5)*60+MINUTE(B5)+SECOND(B5)/60- 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.
=HOUR(B5)*3600+MINUTE(B5)*60+SECOND(B5)- 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.
=TEXT(B5/24,"[h]:mm")- 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
Get FREE Advanced Excel Exercises with Solutions!
3/30/2026 to 4/5/2026 Day In Out Hours
Monday 2:45 PM 1:15 AM 10.30
Tuesday 1:45 PM 12:30 AM 10.45
Wednesday 2:45 PM 12:30 AM 9.45
Thursday 1:45 PM 12:30 AM 10.45
Friday 2:00 PM 12:30 AM 10.30
Saturday
TOTAL HOURS 52.15
please show me how to do the total hours for excel
Hello Afet Redzepi,
Format “In Time” and “Out Time”:
Ensure both columns (In Time and Out Time) are formatted as Time.
You can do this by selecting the cells, right-clicking, choosing Format Cells, and selecting Time.
Formula to calculate hours worked:
In Column D, you can use the formula to calculate the difference between “Out Time” and “In Time.” The formula should be written in the “Hours Worked” column like this:
=TEXT(C2-B2, “h:mm”)
This formula will give the difference in hours and minutes.
Sum the total hours:
In a cell below your data (let’s say D7), use the formula:
=SUM(D2:D6)
This will sum up all the worked hours.
Adjusting for total hours format: If the total hours are not appearing in the right format, make sure the “Hours Worked” cells are formatted correctly to show decimal hours.
You can explore the following article to know more about time calculations: How to Sum Time in Excel (9 Suitable Methods)
Regards,
ExcelDemy