Formula Errors

How to Calculate Hours Worked Across Midnight in Excel

Emma Brown Sep 30, 2026 869 views

Question details

The user needs to calculate the total daily hours for work shifts that start on one day and end on the next day (past midnight), without relying on a separate column for the date.

How to Calculate Hours Worked Across Midnight in Excel

Product: Excel
Device & OS: not provided
Scenario: Tracking employee shift hours, payroll, or personal work schedules when working overnight shifts.
Observed behavior: Standard time subtraction (End Time minus Start Time) results in negative values or error symbols when the end time crosses into the next day.

Before you start

Ensure your start and end times are entered in a recognized time format (e.g., '10:00 PM' and '6:00 AM' or '22:00' and '06:00') so the spreadsheet formulas can process the values mathematically.

Solution 1Recommended

Use the MOD Function to Calculate Overnight Hours

The MOD function is the most efficient and straightforward way to handle time differences that cross midnight, ensuring the calculation always returns a positive time value.

Because spreadsheets store time as fractions of a single 24-hour day, subtracting a late evening time from an early morning time inherently results in a negative value. The MOD function solves this by returning the remainder after division, seamlessly wrapping the calculation around midnight without needing complex logical statements.

Select the result cell

Click on the blank cell where you want the total worked hours to appear (for example, D3).

Enter the MOD formula

Type `=MOD(C3-B3, 1)` into the formula bar, assuming cell B3 contains your start time and cell C3 contains your end time.

Apply time formatting

Press Enter. Right-click the cell, select 'Format Cells', navigate to the 'Number' tab, and choose a 'Time' format such as 'h:mm' to display the hours and minutes correctly.

Use the MOD Function to Calculate Overnight Hours

Convert to Decimal Hours for Payroll: If you need the result expressed as a decimal number (e.g., 8.5 hours instead of 8:30) for wage calculation, update the formula to `=MOD(C3-B3, 1)*24` and format the cell as a General or Decimal number.

Solution 2

Use the IF Function for Logical Time Calculation

An alternative approach uses the IF function to add 1 (a full day) to the end time if it is mathematically smaller than the start time.

Efficient Spreadsheet Solution

Calculate Shift Hours Easily with WPS Spreadsheet

WPS Spreadsheet handles complex time and date calculations flawlessly. You can use the exact same formulas to track regular and overnight shift hours, making employee timesheets and payroll management incredibly simple.

1. Open WPS Spreadsheet: Launch WPS Office and open your timesheet document.
2. Input your times: Enter the shift start time in column B and the end time in column C using standard time formats.
3. Apply the time formula: In column D, select the blank cell, type `=MOD(C3-B3, 1)`, and press Enter.
4. Format as needed: Press Ctrl+1 to open the Format Cells window and select your preferred Time format, or multiply the formula by 24 for decimal hours.

100% compatible with Microsoft Excel formulas and time formattingEasy-to-use Format Cells dialog for custom time trackingBuilt-in templates for timesheets and schedulingFree and lightweight alternative to heavy office suites

microsoft office alternative - wps office

Frequently Asked Questions

Why do I get a string of hash symbols (####) when subtracting times?

Spreadsheets display '####' when a time or date calculation results in a negative number, which happens if you simply subtract an evening start time from a morning end time. Using the `=MOD(End-Start, 1)` formula prevents this negative result and resolves the error.

How do I calculate total pay using the overnight hours?

First, multiply your time calculation formula by 24 to convert the time into a decimal value (e.g., `=MOD(C3-B3, 1)*24`). Then, multiply that decimal by the employee's hourly wage. Ensure the final wage calculation cell is formatted as Currency or Accounting, not Time.

Can these formulas handle shifts that are longer than 24 hours?

No, both the MOD and IF formulas provided assume the shift duration is strictly under 24 hours. If an employee works more than 24 hours consecutively, you must include the specific date and time in the cell (e.g., '10/12/2023 8:00 AM') and simply subtract the start cell from the end cell.

Excel & Data Problems