Formula Errors

How to Calculate Elapsed Time Across Midnight in Excel

Maira Mehtab Sep 20, 2026 868 views

Question details

The user needs a formula to calculate the total time elapsed between a start time and an end time when the period crosses over into the next day.

Product: Excel
Device & OS: not provided
Scenario: Tracking work shifts, project hours, or event durations where the start time is in the evening and the end time is in the morning of the following day.
Observed behavior: Using a standard subtraction formula (End Time minus Start Time) produces a negative time value, which Excel displays as an error (a series of hash marks like ######) because it cannot handle negative times by default.

Before you start

Ensure that your start and end times are entered in a recognized time format (such as hh:mm AM/PM) and that the cells intended for the results are formatted to display time correctly.

Solution 1Recommended

Use a Logical Test Formula

Add a logical test to the standard subtraction formula to automatically add 24 hours if the end time is earlier than the start time.

In Excel, time is calculated as a fraction of a 24-hour day (where 1 equals 24 hours). By checking if the end time is smaller than the start time, you can add 1 to the result to account for the next day.

Select the result cell

Click on the cell where you want to display the elapsed time (for example, C2).

Enter the formula

Type the formula =B2-A2+(B2<A2) (assuming A2 contains the Start Time and B2 contains the End Time) and press Enter.

Apply custom time formatting

Right-click the result cell, select 'Format Cells', navigate to the 'Number' tab, choose 'Custom', and type [h]:mm in the Type box.

Fill the formula down

Click and drag the fill handle at the bottom-right corner of the cell to apply this formula to the rest of your data rows.

Understanding the logic: The expression (B2<A2) evaluates to TRUE (which equals 1) if the shift crosses midnight, adding a full day's value to prevent a negative time error.

Solution 2

Use the MOD Function

An alternative, shorter formula utilizing the MOD function to handle negative time values automatically without a logical test.

Easily Calculate Time with WPS Spreadsheet

WPS Spreadsheet fully supports standard time formulas and custom formatting, making it simple to calculate elapsed hours across midnight for your timesheets and schedules.

1. Open your timesheet: Launch WPS Office and open your spreadsheet containing the start and end times.
2. Input the formula: Select the elapsed time column and enter the formula =MOD(End_Time - Start_Time, 1).
3. Format the cell: Press Ctrl+1, select Custom formatting, and apply [h]:mm to view the exact hours and minutes.

Fully compatible with Microsoft Excel formulas, including MOD and logical tests.Completely free and lightweight alternative to Microsoft Office.Built-in custom cell formatting for seamless time tracking and management.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my spreadsheet show ###### when calculating time differences?

This happens when a time calculation results in a negative number. Because standard time cannot be negative, the spreadsheet displays a row of hashtags. You can fix this by accounting for the day change using a formula like =B2-A2+(B2<A2).

How do I format time to show elapsed hours greater than 24?

You need to use a custom number format. Apply the format [h]:mm to the cell. The brackets around the 'h' instruct the spreadsheet to display cumulative hours (like 26:30) rather than resetting the clock after 24 hours.

Can I calculate time across midnight without using special formulas?

Yes, if your cells contain both the date and the time (e.g., '10/25/2023 10:00 PM' and '10/26/2023 6:00 AM'). In this case, you can simply subtract the start cell from the end cell directly, because the date values automatically account for the change in days.

Excel & Data Problems