Calculate Excel Day and Night Shift Hours Across Midnight
Question details
Calculate the hours of an employee shift that belong to the 6:00 AM–6:00 PM day period and the 6:00 PM–6:00 AM night period when the shift crosses midnight.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A timesheet records employee start and end date-time values and needs separate totals for day and night work periods.
- Observed behavior
- A shift from 7:00 PM to 8:30 AM should return 11 night hours and 2.5 day hours.
Enter the start and end values as full Excel date-time values, not time-only values. For an overnight shift, make sure the end date is the following calendar day so that the end value is greater than the start value.
Use date-time overlap formulas for day and night periods
Use this method for shifts that span one overnight period. It compares the shift against each applicable 6:00 AM–6:00 PM and 6:00 PM–6:00 AM window.
Assume the shift start date-time is in A2 and the shift end date-time is in B2. The formulas return decimal hours. For example, a start of 7:00 PM and an end of 8:30 AM the next day returns 2.5 day hours and 11 night hours.
If a shift can run for more than 24 hours, add overlap terms for each additional calendar day or calculate the result from a daily schedule table.
In A2, enter the start date and time, such as 1/10/2026 7:00 PM. In B2, enter the actual end date and time, such as 1/11/2026 8:30 AM.
In a Day Hours cell, enter =24*(MAX(0,MIN($B2,INT($A2)+TIME(18,0,0))-MAX($A2,INT($A2)+TIME(6,0,0)))+MAX(0,MIN($B2,INT($A2)+1+TIME(18,0,0))-MAX($A2,INT($A2)+1+TIME(6,0,0)))). This sums the overlap with the day windows from 6:00 AM to 6:00 PM.
In a Night Hours cell, enter =24*(MAX(0,MIN($B2,INT($A2)+TIME(6,0,0))-MAX($A2,INT($A2)-1+TIME(18,0,0)))+MAX(0,MIN($B2,INT($A2)+1+TIME(6,0,0))-MAX($A2,INT($A2)+TIME(18,0,0)))). This sums the overlap with the night windows from 6:00 PM to 6:00 AM.
For A2 = 1/10/2026 7:00 PM and B2 = 1/11/2026 8:30 AM, confirm that the Day Hours result is 2.5 and the Night Hours result is 11.
Convert time-only entries into valid overnight date-times
Use this when your worksheet contains only clock times and the end time may be earlier than the start time.
Calculate Overnight Day and Night Hours in WPS Spreadsheets
WPS Spreadsheets lets you enter complete date-time values and use familiar MIN, MAX, INT, and TIME formulas to split overnight work into day and night totals. Open or migrate an existing Excel timesheet and apply the same overlap calculation workflow.
- 1. Open the timesheet: Launch WPS Spreadsheets and select Open to open your existing .xlsx or .xls shift log.
- 2. Enter full date-time values: Place the shift start date-time in column A and the end date-time in column B, ensuring an overnight end uses the following date.
- 3. Add a Day Hours formula: Select a results cell and enter the day-period overlap formula using the 6:00 AM and 6:00 PM TIME values.
- 4. Add a Night Hours formula: Select the next results cell and enter the night-period overlap formula for the 6:00 PM to 6:00 AM window.
- 5. Fill formulas down the schedule: Drag the fill handle down to calculate day and night totals for each employee shift.
Frequently Asked Questions
Why does subtracting the end time from the start time fail for overnight shifts?
Clock times do not include a date. When an end time such as 8:30 AM is treated as the same day as a 7:00 PM start, it appears earlier. Enter full date-time values, or add one day to the end value when B2 is less than A2.
What does the MAX and MIN overlap pattern do in the formula?
MAX selects the later of the shift start and period start, while MIN selects the earlier of the shift end and period end. Their difference is the time that exists in both ranges; MAX(0,...) prevents negative results when there is no overlap.
How do I format the result as decimal hours in Excel?
The formulas multiply the date-time result by 24, so format the result cells with Home > Number and choose Number with the required decimal places, such as 0.0 or 0.00.
Can I use these formulas for a shift that lasts more than 24 hours?
Not without adding more day and night windows. For multi-day shifts, include one overlap term for each calendar day covered, or create a daily schedule table and sum the overlap for every applicable period.
