Calculate Overlapping Unavailable Time for Excel Facility Schedule Intervals
Question details
Calculate the total unavailable time within each scheduled facility interval by identifying the overlap between schedule and unavailability date-time ranges.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A schedule contains start and end times for facilities or employees, while a separate list records unavailable start and end times. The user needs to total only the unavailable time that falls within each scheduled interval.
- Observed behavior
- The required result is the sum of positive overlaps for matching employee and facility records, displayed as an elapsed duration such as [h]:mm.
Confirm that all schedule and unavailability start and end values are real Excel date-time values, not text. Set the result cell format to Custom [h]:mm so totals over 24 hours display correctly.
Use SUMPRODUCT to total matching interval overlaps
Use this method when each schedule row must calculate the total overlap from multiple unavailable intervals for the same employee and facility.
For every matching unavailable record, the overlap begins at the later of the two start times and ends at the earlier of the two end times. Only intervals where the end is later than the start contribute to the total.
This example assumes the schedule row has Employee in A2, Facility in B2, Start in C2, and End in D2. The unavailable list uses Employee in H2:H100, Facility in I2:I100, Start in J2:J100, and End in K2:K100.
In the schedule table, add a result column such as Unavailable Time, then select the first result cell for the schedule row, for example E2.
Enter =SUMPRODUCT(($H$2:$H$100=$A2)*($I$2:$I$100=$B2)*($K$2:$K$100>$C2)*($J$2:$J$100<$D2)*(IF($K$2:$K$100<$D2,$K$2:$K$100,$D2)-IF($J$2:$J$100>$C2,$J$2:$J$100,$C2))) to sum the overlap for matching employee and facility intervals.
Press Enter, then drag the fill handle from E2 down the schedule result column to calculate unavailable time for every scheduled interval.
Select the result cells, open Format Cells, choose Custom, and enter [h]:mm in the Type box. This displays accumulated hours correctly beyond 24 hours.
Calculate the overlap for one pair of schedule and unavailable intervals
Use this simpler formula when each schedule interval corresponds to only one unavailable interval or when checking the logic before applying it to a larger table.
Calculate Schedule and Unavailability Overlaps in WPS Spreadsheet
WPS Spreadsheet can calculate the unavailable portion of each facility schedule with date-time formulas and SUMPRODUCT. Match employee and facility values, compare each pair of interval boundaries, and format the final total as elapsed hours and minutes.
- 1. Open the schedule workbook: Open the workbook in WPS Office and select the worksheet containing the scheduled and unavailable date-time lists.
- 2. Verify the date-time columns: Select the start and end columns, then use Home > Number Format to apply a date and time format so the values can be used in calculations.
- 3. Add the overlap formula: Click the first Unavailable Time result cell and enter a SUMPRODUCT formula that matches employee and facility values and calculates MAX start to MIN end overlap.
- 4. Fill results for all scheduled rows: Press Enter, then drag the cell fill handle down to copy the formula through the remaining schedule rows.
- 5. Display totals as elapsed time: Select the result column and choose Home > Number Format > More Number Formats, then apply the custom format [h]:mm.
Frequently Asked Questions
What is the formula for overlap between two Excel date-time intervals?
Use =MAX(0,MIN(schedule_end,unavailable_end)-MAX(schedule_start,unavailable_start)). MIN finds the earlier end, MAX finds the later start, and MAX(0,...) prevents negative results for non-overlapping periods.
How do I calculate overlaps only for the same facility and employee?
Add employee and facility tests to SUMPRODUCT, such as (UnavailableEmployee=ScheduleEmployee)*(UnavailableFacility=ScheduleFacility), before multiplying by the overlap duration expression.
Why does my unavailable-time result show a decimal instead of hours and minutes?
Excel stores time as a fraction of a day. Select the result cells, open Format Cells, choose Custom, and use [h]:mm to display the value as elapsed hours and minutes.
Can SUMPRODUCT calculate overlap across hundreds of unavailable records?
Yes. Use fixed ranges that cover the unavailable table, such as $H$2:$H$1000, and copy the formula down the schedule rows. For best performance, avoid using whole-column references in large workbooks.
Will overlapping unavailable entries be counted twice?
Yes. SUMPRODUCT adds each matching unavailable row independently. If unavailable records overlap each other, first merge the overlapping intervals or use a separate consolidation step to prevent duplicate time from being included.
