Calculation Issues

How to Calculate Timecard Hours in Tenths in Excel

Huda Qurayshi Sep 28, 2026 868 views

Question details

The user needs to calculate the total time elapsed between a start time and an end time, and output the result as hours rounded down to the nearest tenth.

How to Calculate Timecard Hours in Tenths of an Hour in Excel

Product: Excel
Device & OS: not provided
Scenario: Managing employee timecards or billing logs where hours are billed in 0.1-hour (six-minute) increments.
Observed behavior: Requires converting standard time durations (e.g., 07:05 to 15:45) into decimal hour values (like 8.6) while handling overnight shifts properly.

Before you start

Verify that your start and end time cells are formatted as standard Time or Custom (h:mm) formats so the calculation recognizes them properly.

Solution 1Recommended

Use ROUNDDOWN and MOD Functions to Calculate Decimal Hours

This method converts the time difference into decimal hours, accurately rounding down to the nearest tenth and naturally handling overnight shifts.

Excel stores time values as fractions of a 24-hour day. By subtracting the start time from the end time and multiplying by 24, you can convert this fraction into standard hours. Incorporating the MOD function ensures the formula continues to work perfectly even if an employee's shift crosses midnight.

Enter Time Data

Input your arrival time in cell B2 and your finish time in cell C2. Ensure they are entered in a recognized time format (e.g., 07:05 and 15:45).

Apply the Formula

Select the cell where you want the calculated decimal hours to appear, and enter the following formula: =ROUNDDOWN(24*MOD(C2-B2,1),1)

Format as Number

Press Enter to apply the formula. If the result displays as a time (e.g., 08:36), right-click the cell, choose 'Format Cells', and change the format to 'Number' with 1 decimal place.

Use ROUNDDOWN and MOD Functions to Calculate Decimal Hours

Overnight Shift Support: The MOD(..., 1) portion of the formula acts as a safety net that seamlessly calculates total hours when a shift goes past midnight (e.g., 22:00 to 06:00) without resulting in a negative number.

Calculate Timesheets Easily with WPS Spreadsheet

WPS Spreadsheet fully supports advanced time calculations, including the MOD and ROUNDDOWN functions, making payroll management incredibly simple.

1. Open your Timesheet: Launch WPS Spreadsheet and open your timecard document containing the start and end times.
2. Input the Formula: Select the target cell and type =ROUNDDOWN(24*MOD(C2-B2,1),1) to convert the shift into decimal hours.
3. Apply to All Rows: Press Enter, then drag the fill handle down to apply this calculation to the rest of the employee records.

Fully compatible with Microsoft Excel formulas and time formatting.Features a built-in function library for flawless date and time calculations.Lightweight, user-friendly, and completely free to download.

microsoft office alternative - wps office

Frequently Asked Questions

Why do I need to multiply by 24 in the formula?

In Excel, 1 whole unit equals 24 hours (a full day), so 1 hour is stored as 1/24. Multiplying the time difference by 24 converts the fractional day value into a standard decimal hour format.

How can I round to the nearest tenth instead of rounding down?

If your company rounds to the nearest 6-minute increment rather than strictly rounding down, replace ROUNDDOWN with the standard ROUND function. Your formula will look like this: =ROUND(24*MOD(C2-B2,1),1).

What if my timecard entries include both date and time?

If your cells contain full date and time values (e.g., 10/25/2023 07:05), the MOD function is unnecessary. You can simply subtract the start time from the end time and multiply by 24, like this: =ROUNDDOWN((C2-B2)*24, 1).

Excel & Data Problems