ExcelFlux

Calculate hours for a shift that crosses midnight

Enter the start and end as dates and times, then subtract the break. A 22:00–06:00 shift with a 30-minute break lasts 7 hours and 30 minutes.

Worked example

B2Start11/30/2026 22:00
C2End12/01/2026 06:00
D2Break30 min
E2Net time7.50 hours

Enter the start and end as Excel date and time values.

Download Excel workbook

Copyable formulas

English Excel · en-US
=IF(COUNT(B2:C2)=2,(ROUND((C2-B2)*1440,0)-D2)/60,"")
Turkish Excel · tr-TR
=EĞER(BAĞ_DEĞ_SAY(B2:C2)=2;(YUVARLA((C2-B2)*1440;0)-D2)/60;"")

How the formula works

  1. Subtract the start from the end. Excel stores the difference in days. For an overnight shift, enter the end time with the following day’s date.
  2. Convert the difference to minutes. A day has 1,440 minutes. Round to a whole minute, then subtract the break.
  3. Convert minutes to hours. Divide by 60. 7.50 hours equals 7 hours and 30 minutes.

Missing or invalid entries

The result stays blank if the start or end is missing. A blank break cell counts as zero minutes. The workbook’s Check column flags an end before the start or a break longer than the shift.

Reporting across two months

The formula returns the net time for the whole shift. To report by month, split the shift where the month changes. If the break is 02:00–02:30 in this example, report 2 hours in November and 5 hours 30 minutes in December.

Microsoft Excel reference