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
B2 | Start | 11/30/2026 22:00 |
|---|---|---|
C2 | End | 12/01/2026 06:00 |
D2 | Break | 30 min |
E2 | Net time | 7.50 hours |
Enter the start and end as Excel date and time values.
Download Excel workbookCopyable 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
- 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.
- Convert the difference to minutes. A day has 1,440 minutes. Round to a whole minute, then subtract the break.
- 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.