How to Calculate a Time Difference Across Midnight in Excel

To calculate a time difference across midnight in Excel, subtract the start time from the end time and account for the day change.

A simple formula can handle recurring overnight shifts, while date-and-time values give better results when shifts may last longer than 24 hours.

Use MOD to calculate time across midnight

If the start and end times are in separate cells and the shift ends the next day, use =MOD(B2-A2,1).

This returns the elapsed time as a fraction of a day, even when the end time is earlier on the clock than the start time.

Format C2 with the custom number format [h]:mm to display 8:00.

The square brackets let Excel display total hours rather than resetting the hour count after 24.

For a result in decimal hours, multiply by 24: =MOD(B2-A2,1)*24.

In this example, the result is 8.

Excel stores times as portions of a 24-hour day: midnight is 0 and noon is 0.5.

MOD returns the remainder after division by one day, so a negative time difference wraps to the correct overnight duration.

This also means the formula assumes the end time is less than 24 hours after the start.

Use an IF formula as an alternative

If you prefer to make the midnight adjustment explicit, use =IF(B2<A2,B2+1-A2,B2-A2).

When B2 is earlier than A2, Excel adds one day to the end time before subtracting the start.

Otherwise, it subtracts the times normally.

For a decimal-hour result, use =IF(B2<A2,(B2+1-A2)*24,(B2-A2)*24).

The IF version is useful for explaining the logic, but for a standard overnight shift that ends the next day, MOD is shorter and easier to fill down a column.

Choose a format that shows the result correctly

Excel may display a valid duration as a clock time or show #### if the result cell uses an unsuitable format.

Select the result cells, open Format Cells, choose Custom, and enter [h]:mm for hours and minutes.

Use [h]:mm:ss if seconds matter.

For decimal hours, format the result as Number instead.

A result of 8.5 means eight and a half hours, or 8 hours and 30 minutes.

Avoid formatting a decimal-hour result as Time, because Excel will interpret it as a day fraction and display a clock value.

Calculate a shift when dates are available

If the start and end timestamps include dates, subtract the complete values: =B2-A2.

For example, if A2 contains 11/3/2026 10:00 PM and B2 contains 11/4/2026 6:00 AM, format the result as [h]:mm to show 8:00.

To return decimal hours from timestamps, multiply the difference by 24: =(B2-A2)*24.

Using dates is more reliable when shifts can run beyond midnight more than once, last 24 hours or longer, or have an end time that might be on a different day than expected.

MOD would wrap any duration to less than one day.

Handle overnight time calculations with breaks

For a shift with an unpaid break recorded as a duration, subtract the break from the elapsed time.

If A2 is the start, B2 the end, and C2 contains a break of 30 minutes, use =MOD(B2-A2,1)-C2.

Format the result as [h]:mm; an 8-hour shift with a 30-minute break displays 7:30.

If the break is entered as a number of minutes rather than an Excel time, convert minutes to a day fraction by dividing by 1440: =MOD(B2-A2,1)-C2/1440.

There are 1,440 minutes in a day.

To return the net result in decimal hours, use =(MOD(B2-A2,1)-C2/1440)*24.

Common problems and edge cases

• End time equals start time: =MOD(B2-A2,1) returns zero.
  
  If equal times mean a full 24-hour shift in your data, use date-and-time values or add a rule that distinguishes a full-day shift from a zero-duration entry.
• Text instead of time values: A value that looks like 10:00 PM may be stored as text, especially after importing data.
  
  Convert it to a recognized time value before subtracting; number formats alone cannot turn text into a time.
• Unexpected results over 24 hours: MOD always returns less than one day.
  
  Use full timestamps with =B2-A2 for longer durations.
• Negative durations: Subtracting a break longer than the shift can produce a negative result.
  
  Validate inputs or use a check such as =IF(C2>MOD(B2-A2,1),"Check break",MOD(B2-A2,1)-C2).
• Regional date settings: Excel may interpret typed dates differently based on system locale.
  
  Use dates entered with your locale’s expected order, or build timestamps from cells containing validated date and time values.

Excel and Google Sheets compatibility

The MOD, IF, subtraction, and multiplication formulas shown here work in current desktop and web versions of Microsoft Excel and Google Sheets.

Both spreadsheet apps use day fractions for time calculations and support the [h]:mm duration format in typical spreadsheet workflows.

If a custom format displays differently in your environment, check the application’s number-format settings and use decimal hours as a straightforward alternative.

For a repeating overnight-shift table, enter =MOD(B2-A2,1) in C2, format the column as [h]:mm, and fill the formula down.

Use full date-and-time entries whenever the calendar date affects the intended duration.