Function Problems

How to Calculate In-Hours and Out-of-Hours Work with Excel Formulas

Bushra Parveen Sep 25, 2026 870 views

Question details

The user needs to use Excel formulas to calculate total work hours separately for in-hours and out-of-hours shifts based on a status cell.

How to Calculate In-Hours and Out-of-Hours Work with Excel Formulas

Product: Excel
Device & OS: not provided
Scenario: Tracking employee shifts, timesheets, or billable time by automatically separating regular working hours from overtime using start and finish times.
Observed behavior: The formula needs to subtract the start time from the finish time when a specific status (IH or OH) is detected, while returning a blank cell for unmatched rows.

Before you start

Ensure your start time and finish time columns are formatted as 'Time' or 'Custom (h:mm)' before applying the formulas so the time differences calculate correctly.

Solution 1Recommended

Use the IF Function to Separate Time Calculations

Apply an IF formula to check the status cell (IH or OH) and selectively subtract the start time from the finish time.

By utilizing the IF function, Excel evaluates whether a given cell meets your condition (such as containing 'IH' for In-Hours or 'OH' for Out-of-Hours). If the condition is met, it performs the time subtraction; otherwise, it outputs an empty string, keeping your spreadsheet clean.

Enter the In-Hours formula

Select the cell where you want the In-Hours total to appear (e.g., D2). Type the formula =IF(A2="IH", C2-B2, ""), assuming A2 is the status, B2 is the start time, and C2 is the finish time.

Enter the Out-of-Hours formula

Select the cell for the Out-of-Hours total (e.g., E2) and enter the formula =IF(A2="OH", C2-B2, ""). Press Enter to apply.

Apply custom time formatting

Select both result columns, right-click, and choose 'Format Cells'. Under the 'Custom' category, type [h]:mm and click OK. This ensures that cumulative totals exceeding 24 hours display correctly without resetting to zero.

Use the IF Function to Separate Time Calculations

Formatting Tip: Using the [h]:mm format is essential for timesheets. Standard time formats will reset to 0:00 once the tracked hours pass the 24-hour mark.

Smart Time Tracking

Calculate Time Differences Easily with WPS Spreadsheet

You can perform the exact same time tracking and IF function calculations smoothly using WPS Office, a highly compatible, lightweight, and completely free alternative to Microsoft Excel.

1. Open your timesheet: Launch WPS Spreadsheet and open your existing timesheet or shift tracker.
2. Apply the conditional formula: Enter =IF(A2="IH", C2-B2, "") in your designated In-Hours column and drag the fill handle down to apply it to all rows.
3. Format cells for cumulative hours: Right-click the calculated cells, select 'Format Cells', navigate to 'Custom', and apply the [h]:mm format to accurately sum hours beyond a standard day.

100% compatible with Microsoft Excel formulas and time formattingBuilt-in custom number formats like [h]:mm for accurate timesheetsLightweight application that runs smoothly on any deviceRich library of free templates for timesheets and shift scheduling

microsoft office alternative - wps office

Frequently Asked Questions

Why does my time calculation show as a decimal instead of hours?

Spreadsheet software stores time as a fraction of a 24-hour day (e.g., 12 hours is 0.5). To fix this, right-click the cell, select 'Format Cells', and change the format to a custom time format like h:mm.

How do I calculate time if the shift crosses past midnight?

If a shift ends the next day, subtracting the start time from the finish time results in a negative number or error. Wrap the calculation in the MOD function like this: =IF(A2="IH", MOD(C2-B2, 1), "") to ensure the result is always correctly calculated as positive time.

Can I sum the total in-hours and out-of-hours for the whole week?

Yes, you can use the SUM function at the bottom of your In-Hours and Out-of-Hours columns (e.g., =SUM(D2:D8)). You must ensure the total cell is formatted as [h]:mm so that accumulated time does not visually reset after reaching 24 hours.

Excel & Data Problems