Excel Time Formulas: Calculate Overnight PM-to-AM Hours

Formulas & functions · Updated November 18, 2026

If A2 contains a PM start time and B2 contains the following AM finish time, enter =MOD(B2-A2,1) to calculate the elapsed time across midnight. For example, a shift from 10:30 PM to 6:15 AM lasts 7 hours and 45 minutes; format the formula cell as h:mm or [h]:mm to display the result clearly.

Why PM-to-AM calculations need special handling

Excel stores times as fractions of a day: midnight is zero, noon is one-half, and the next midnight is one whole day. The clock display hides that underlying number. As a result, 10:30 PM is later on the clock than 6:15 AM, even though the 6:15 AM finish may occur on the next calendar day.

Subtracting the start time directly from the finish time would produce a negative fraction for an overnight shift. Excel may display that result as a row of number signs, or show a negative value, depending on the workbook’s date system and formatting. The MOD formula wraps the negative difference into the remaining fraction of a 24-hour day. That is why it works for a PM start followed by an AM finish.

This calculation is useful for work shifts, overnight travel, equipment run times, and any other interval recorded as a start and finish time. It calculates elapsed duration, not a clock time or a date.

Set up the cells and enter the formula

Put the start time in A2 and the finish time in B2. Enter times with AM or PM so Excel interprets them correctly, such as 10:30 PM in A2 and 6:15 AM in B2. In C2, enter =MOD(B2-A2,1). With those example times, the result is 7:45, meaning seven hours and 45 minutes.

To make the result display as a duration, select C2 and choose Home > Number Format, then select a time format such as 13:30. If you prefer to set a custom format, open Format Cells with Ctrl+1 on Windows, choose Number > Custom, and enter h:mm in the Type box. On a Mac, Command+1 opens Format Cells. The formula itself is the same on Windows, Mac, and Excel for the web.

To apply the calculation to more rows, copy or fill the formula down column C. Excel adjusts the row references automatically: the next row will refer to A3 and B3. Check that the start and finish cells contain actual Excel time values rather than text. A time entered in a normal cell is usually recognized automatically, but imported data or a leading apostrophe can leave a time stored as text.

How MOD handles the midnight boundary

The MOD function returns a remainder after division. In this formula, the difference between finish and start is expressed in days, and the second argument, 1, means one full day. If the finish is earlier than the start, the raw subtraction is negative; MOD moves the result into the range from zero up to, but not including, one day.

For example, 6:15 AM minus 10:30 PM is negative 16 hours and 15 minutes when Excel treats both values as times on the same date. Wrapping that difference around a 24-hour day gives 7 hours and 45 minutes. If the finish time is later than the start time on the same day, MOD leaves the ordinary positive duration unchanged.

That convenience comes with an assumption: the interval is less than 24 hours, or at least that you want the answer reduced to a single-day remainder. If both times are identical, MOD returns zero. It cannot tell whether equal clock readings mean no elapsed time, exactly 24 hours, or several days. When that distinction matters, record dates as well as times.

Choose a formula that matches the data

For time-only entries and a shift that crosses midnight, MOD is usually the clearest option. There is also a conditional formula that adds one day only when the finish time is earlier than the start time.

With the start time in A2 and the finish time in B2, use =IF(B2<A2,B2+1-A2,B2-A2). For a 10:30 PM start and 6:15 AM finish, it returns 7:45. If the finish is later on the same day, it simply subtracts the start from the finish. This version makes the overnight assumption visible, but it still cannot identify a duration of 24 hours or more from time-only values.

If the cells contain complete date-and-time values rather than clock times alone, subtract the start from the finish directly. With the start date and time in A2 and the finish date and time in B2, use =B2-A2. For a start of November 2 at 10:30 PM and a finish of November 3 at 6:15 AM, the result is 7:45. Because the dates establish which day each time belongs to, this approach also handles intervals longer than one day.

Do not apply MOD to date-and-time values if you need to preserve whole days: MOD would discard the day portion of the elapsed duration. Choose the formula based on what the cells actually contain, not just on how they appear after formatting.

Format results as durations, not clock readings

Excel separates a value from its display format. A calculated duration of 7 hours and 45 minutes is stored as a fraction of a day, whether it appears as 7:45, 7:45 AM, or a decimal. Formatting changes how the result looks; it does not change the elapsed time.

For a single overnight shift, h:mm is a compact display. For totals that might reach 24 hours, use the custom format [h]:mm. The square brackets tell Excel to show total elapsed hours instead of restarting the hour count at zero each day. For instance, a total of 27 hours and 30 minutes appears as 27:30 with [h]:mm; a clock-style hour format may show 3:30 instead.

If you want to show a clock time with an AM or PM label rather than an elapsed duration, use a format such as h:mm AM/PM. That format is appropriate for a start or finish time, not usually for a shift length: a result displayed as 7:45 AM could be mistaken for a time of day rather than seven hours and 45 minutes.

Common errors and how to fix them

  • Negative values or number signs: Check that the formula accounts for midnight and that the result cell has a suitable time format. For time-only entries, try the MOD formula.
  • A result that looks like a morning clock time: Change the result format from an AM/PM clock format to h:mm or [h]:mm, depending on whether totals can exceed 24 hours.
  • A result of zero when a full day was worked: Time-only entries cannot distinguish equal clock readings from a 24-hour interval. Include dates and subtract the date-and-time values.
  • An unexpected result from imported times: Confirm the inputs are numeric time values. Text that looks like a time may not subtract correctly; re-enter it as a time or convert the source data before calculating.
  • Confusing decimal hours with hours and minutes: A value of 7.5 hours means 7 hours and 30 minutes, not 7 hours and 50 minutes. Excel’s time fraction for 7:30 is 0.3125 of a day. Use a duration format for readability.
  • Adding a lunch break incorrectly: If a break is recorded separately, subtract it as a duration from the shift result. Make sure the break is entered as a time duration, not as a clock time such as 12:30 PM.

Calculate paid hours and totals carefully

For a simple shift log, use one row per shift: start time, finish time, and calculated duration. Format each duration as [h]:mm if you will add shifts together. A sum of durations can exceed 24 hours, and an ordinary clock format may wrap the display, making the total look smaller than it is.

For example, two shifts of 7:45 and 8:30 total 16:15. If the total is entered using a SUM formula, the cell should use [h]:mm so that larger totals remain cumulative. If a payroll system needs decimal hours, convert the duration to hours by multiplying the Excel time value by 24. Keep the original duration available so the decimal conversion does not obscure minutes.

Real shift records may need more than a start and finish: breaks, multiple work periods, daylight-saving clock changes, and different dates can affect the interpretation. In particular, a clock change can make the elapsed time differ from the apparent difference between local clock readings. For those records, use actual date-time data and the rules required by the employer or timekeeping system rather than relying on two clock times alone.

Frequently Asked Questions

How do I calculate hours from 10 PM to 6 AM in Excel?
With the start time in A2 and the finish time in B2, enter =MOD(B2-A2,1) in C2. Format C2 as h:mm or [h]:mm. The result is 8:00.

Why does Excel show a negative time for an overnight shift?
Direct subtraction treats both time-only values as belonging to the same day. If the finish is earlier on the clock than the start, the difference is negative. MOD wraps that difference across midnight, provided the interval is intended to be less than 24 hours.

Can Excel calculate an overnight shift longer than 24 hours?
Yes, if the cells include the actual start and finish dates as well as times. Subtract the start date-time from the finish date-time, then use the custom format [h]:mm to display all elapsed hours. Two time-only values do not contain enough information to determine how many days passed.

Should I use h:mm or [h]:mm?
Use h:mm for a duration that will stay under 24 hours. Use [h]:mm for accumulated hours or any duration that could reach 24 hours, because the brackets prevent the hour display from resetting each day.

How do I turn an Excel duration into decimal hours?
Multiply the duration value by 24, because Excel stores one day as 1. For example, 7:30 becomes 7.5 decimal hours. Format the converted result as a number rather than a time so it displays as a decimal.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.