Function Problems

How to Calculate Early or Late Departure Time in Excel

Maira Mehtab Sep 22, 2026 869 views

Question details

The user needs an Excel formula to calculate the exact time difference between a scheduled shift end and an employee's actual departure time.

Product: Excel
Device & OS: not provided
Scenario: Tracking employee attendance and calculating how many hours or minutes an employee left early or stayed late based on their scheduled shift.
Observed behavior: Requires a formula that accurately outputs the difference as a time value (e.g., calculating the 9-minute difference between 1:21 PM and 1:30 PM).

Before you start

Ensure that your cells containing the scheduled and actual times are properly formatted as 'Time' in Excel, otherwise the subtraction formulas may return incorrect decimal values or errors.

Solution 1Recommended

Use Simple Subtraction to Calculate Time Difference

The most straightforward way to find out how early or late an employee left is by subtracting the actual departure time from the scheduled end time.

Excel stores times as fractional days, allowing you to easily subtract one time from another. If you subtract the actual time from the scheduled time, a positive result indicates an early departure. Conversely, subtracting the scheduled time from the actual time shows late departure duration.

Enter the basic subtraction formula

If the scheduled end time is in cell A2 and the actual departure time is in cell B2, click on a blank cell and type =A2-B2 to calculate the amount of time the employee left early. To show late departures as a positive number, use =B2-A2.

Apply custom time formatting

Right-click the result cell, select 'Format Cells', go to the 'Custom' category, and type [h]:mm in the type box. Click OK to display the result accurately in hours and minutes.

Solution 2

Calculate Absolute Time Difference with the ABS Function

Use the ABS function when you only need to know the total duration of the deviation (whether early or late) without triggering negative time errors.

Easily Calculate Employee Time Sheets in WPS Spreadsheet

WPS Spreadsheet fully supports all Excel time calculation formulas, including ABS and custom time formatting. It provides a lightweight, highly compatible platform to manage employee attendance effortlessly.

1. Open your timesheet: Launch WPS Spreadsheet and open your existing attendance record or timesheet workbook.
2. Input the time formula: Select the cell where you want the result, and type =ABS(B2-A2) or your preferred time subtraction formula.
3. Format the result: Press Ctrl+1 to open the Format Cells dialog, select Custom, type [h]:mm, and click OK to view your calculated hours.

Fully compatible with Microsoft Excel (.xlsx) formats and formulasEasy-to-use custom cell formatting for accurate time trackingLightweight application that runs smoothly on any deviceFree built-in templates for timesheets and attendance tracking

Frequently Asked Questions

Why does my time calculation show as a decimal number?

Excel calculates time as a fraction of a 24-hour day. To see it as hours and minutes, you must change the cell format. Right-click the cell, choose Format Cells, select Custom, and enter [h]:mm.

Why am I getting '####' when subtracting times?

This happens when the subtraction results in a negative time value, which Excel cannot display by default using the standard 1900 date system. You can fix this by using the =ABS(time1 - time2) formula to get the absolute time difference.

How do I calculate total hours worked?

Subtract the actual clock-in time from the actual clock-out time (e.g., =ClockOut - ClockIn). Ensure both cells are formatted as Time, and format your formula result cell as [h]:mm to see the exact duration the employee was present.

Excel & Data Problems