Calculation Issues

How to Calculate Working Time Across Overnight Shifts in Excel

Maira Mehtab Sep 24, 2026 870 views

Question details

Calculate elapsed task time across overnight shifts using formulas while excluding nonworking periods and weekends.

Product: Excel
Device & OS: not provided
Scenario: Measuring precise task times or employee shifts operating on a specific schedule from Monday 6:00 PM to Saturday 3:00 AM.
Observed behavior: The calculation must accurately measure the overlap between the task interval and defined working hours, properly handling midnight crossovers and omitting weekend hours.

Before you start

Ensure all your start and end times are properly formatted as Date and Time (e.g., mm/dd/yyyy hh:mm AM/PM) so formulas can correctly compute the numerical differences.

Solution 1Recommended

Use MOD and Conditional Formulas for Overnight Working Time

Combine mathematical and date formulas to evaluate overlapping time frames and successfully handle shifts crossing midnight.

Calculating working time for overnight shifts requires adjusting standard math to handle the midnight crossover, as subtracting an earlier morning time from an evening time usually yields a negative error.

By defining your specific working schedule, you can use helper columns to evaluate how much of a task's duration falls exclusively within the 6:00 PM to 3:00 AM window.

Input the Date and Time Data

Enter the task start time in cell A2 and the end time in cell B2. Make sure they include both the date and the time.

Handle the Midnight Crossover

Use the formula =MOD(B2-A2, 1) in an adjacent cell. The MOD function will return the correct positive duration even when the shift crosses midnight.

Exclude Weekend Dates

Apply the =NETWORKDAYS(A2, B2) function to find the total number of standard working days within the period. This helps strip out Saturday and Sunday entirely.

Calculate Schedule Overlap

Use an IF formula to check if the time values fall outside the Monday 6:00 PM to Saturday 3:00 AM window, and subtract those non-working daytime hours from your overall duration.

Use Helper Columns: For highly specific schedules (like excluding daytime hours between 3:00 AM and 6:00 PM), it is best to calculate the total duration, weekend deductions, and daytime deductions in separate helper columns before summing them.

Simplify Time Tracking

Easily Calculate Shift Hours with WPS Spreadsheet

WPS Spreadsheet provides powerful date and time functions to help you accurately track employee working hours, handle overnight shifts, and exclude weekends seamlessly.

1. Enter Your Time Data: Open WPS Spreadsheet and input your start and end date-time records into separate columns.
2. Apply Custom Formatting: Select your data cells, press Ctrl+1 to open Format Cells, and select a Date/Time format to ensure accurate calculations.
3. Insert the Time Formula: Type your MOD and NETWORKDAYS formulas into the designated total hours column to calculate the overlap.
4. Format Results to Exceed 24 Hours: Select your formula results, open Format Cells, and apply the custom format [h]:mm to prevent durations longer than 24 hours from rolling over.

Fully compatible with Microsoft Excel formulas like NETWORKDAYS and MODBuilt-in customizable date and time cell formattingLightweight application with high performance for large payroll datasets

Frequently Asked Questions

Why does my overnight time calculation return a negative number or error?

If you simply subtract a later end time (e.g., 3:00 AM) from an earlier start time (e.g., 6:00 PM), spreadsheet software recognizes it as a negative value. Using the MOD(End-Start, 1) function forces the calculation to account for a 24-hour cycle and returns the correct positive time.

How do I format cells to display total accumulated hours over 24?

Select the cells containing your calculated durations, press Ctrl+1 to open the Format Cells dialog, go to Custom, and enter [h]:mm. The square brackets ensure the hours can accumulate past 24 without rolling over to a new day.

Can I exclude custom holidays from my time tracking calculation?

Yes, the NETWORKDAYS function includes an optional 'holidays' argument. You can create a list of custom holiday dates in another range of your worksheet and reference that range in your formula to automatically exclude them from the total working time.

Excel & Data Problems