Function Problems

Calculate Overlapping Leave Days Excluding Weekends in Excel

Kushani Nimanthika Oct 21, 2026 868 views

Question details

The user needs an Excel formula to calculate the overlapping days between two leave periods, specifically ensuring that weekends, public holidays, and Earned Days Off (EDO) are excluded from the vacation total.

Calculate Overlapping Leave Days Excluding Weekends in Excel

Product: Excel
Device & OS: not provided
Scenario: Calculating net overlapping vacation days for HR or leave management tracking where non-working days must be omitted.
Observed behavior: Finding the exact number of overlapping working days accurately using date logic without counting weekends, holidays, or EDO dates.

Before you start

Ensure your Excel table is organized with distinct columns for Start Date, End Date, and Leave Type, and that you have a separate list of specific holiday dates prepared in your workbook.

Solution 1Recommended

Use NETWORKDAYS.INTL with MAX and MIN logic

Combine the overlap calculation logic with NETWORKDAYS.INTL to identify shared working days and automatically exclude weekends and listed holidays.

The standard overlapping logic uses the MIN function on the end dates and the MAX function on the start dates to find the intersecting timeframe. By wrapping these boundaries inside the NETWORKDAYS.INTL function, you can strip out weekends and custom holidays simultaneously.

After getting the total overlapping working days, you can subtract any Earned Days Off (EDO) that fall within that specific period to get the final net vacation days.

Define the Overlap Start and End Dates

Use MAX(StartDate1, StartDate2) to determine the latest starting date and MIN(EndDate1, EndDate2) to find the earliest ending date of the two leave periods.

Apply the NETWORKDAYS.INTL Function

Use the formula structure =NETWORKDAYS.INTL(OverlapStart, OverlapEnd, 1, HolidayRange). The '1' specifies standard Saturday and Sunday weekends. Replace 'HolidayRange' with the cells containing your public holidays.

Subtract EDO Days

If the leave type includes Earned Days Off, add a condition (e.g., using COUNTIFS) to subtract those specific EDO dates from the total calculated working days.

Enter into the Overlap Days Column

Select the first cell in your dedicated Overlap Days column (such as F2), paste the completed formula, and press Enter. Drag the fill handle down to apply it to all rows in your table.

Use NETWORKDAYS.INTL with MAX and MIN logic

Lock Your Holiday References: Always use absolute references for your holiday list (e.g., $Z$2:$Z$20). This prevents the holiday range from shifting when you drag the formula down your table.

Manage Leave Schedules Efficiently

Calculate Leave Overlaps Easily in WPS Spreadsheet

WPS Spreadsheet fully supports advanced date calculations, including NETWORKDAYS.INTL, MIN, and MAX. It provides a reliable, seamless environment to track employee leave, handle holiday exclusions, and manage complex HR formulas with ease.

1. Open Your HR Roster: Launch WPS Spreadsheet and open your existing employee leave tracking workbook.
2. Select the Target Cell: Click on the cell in the 'Overlap Days' column where you want the calculation to appear.
3. Input the Date Formula: Type your combined =NETWORKDAYS.INTL(...) and MAX/MIN formula, referencing your start dates, end dates, and holiday array.
4. Apply to the Whole Column: Press Enter to see the result, then click and drag the bottom-right corner of the cell to copy the formula to the rest of the column.

Fully compatible with Microsoft Excel date and time functions.Free, lightweight, and fast for handling large datasets and leave tables.Intuitive formula builder helps you troubleshoot complex date overlaps quickly.Seamless format compatibility ensures your .xlsx files work perfectly without data loss.

microsoft office alternative - wps office

Frequently Asked Questions

What does the NETWORKDAYS.INTL function do?

NETWORKDAYS.INTL calculates the number of working days between a specified start and end date. It allows you to customize which days of the week are considered weekends and lets you exclude a specific range of custom holiday dates.

How do I calculate basic date overlaps in Excel?

You can find the total overlapping days between two date intervals using the formula =MAX(0, MIN(EndDate1, EndDate2) - MAX(StartDate1, StartDate2) + 1). This returns the pure day count, but does not exclude weekends natively.

Why is my formula returning a negative number for overlaps?

If there is no overlap between the two date ranges, the MIN minus MAX logic can result in a negative number. Wrapping the logic in a MAX(0, ...) formula ensures that non-overlapping dates correctly return 0 instead of a negative value.

Can I use standard NETWORKDAYS instead of NETWORKDAYS.INTL?

Yes, standard NETWORKDAYS works if your weekends are always Saturday and Sunday. However, NETWORKDAYS.INTL is recommended as it offers more flexibility if you need to define non-standard weekend patterns (like Friday and Saturday).

Excel & Data Problems