Formula to SUM "On Hold" time overlaps (minus non-work time, weekends & public holidays)

[JoeC]

Hi. I have a spreadsheet with two sheets, Sheet1 holds my main data set which is an audit trail of incident assignments each row representing an assignment to a support group. The key columns are Incident Number (column A), Assignment Start Date/Time (column B) and Assignment End Date/Time (column C). My second sheet, Sheet2 holds an audit trail of on-hold status's for incidents. The key columns are Incident Number (column A), Start Date/Time (column B) and End Date/Time (column C).

My objective is to calculate the total incident assignment time for each assignment data row on Sheet1 minus any applicable time the incident spent on hold during its assignment. Furthermore, the time must be calculated in decimal hours and exclude non-work time (work time = 9am-5pm defined in DayStart & DayEnd), exclude Saturdays & Sundays and exclude my Public Holiday list (defined in HolidayList).

So far I have managed to find formulas to calculate to following on Sheet1:

• Assigned Hours (decimal hours) - Simple calculation of (Assignment End Date/Time - Assignment Start Date/Time) * 24
• Actual Assigned Hours (decimal hours) - A more convoluted NETWORKDAYS.INTL formula I found to calculate only time between 9am-5pm, excluding Saturday & Sunday, excluding Public Holidays in HolidayList
• Overlap Found - Checks and counts if a period of time exists on Sheet2 for the incident that falls during the assignment time
• Total Overlap ("On Hold") time (decimal hours) - Using a modified version of the formula in Overlap Found to SUM only the "On Hold" time that fell within the assignment period

My challenge now is do the same as the Total Overlap ("On Hold") time (decimal hours) but to ensure that only Sheet2 time that covers work time (9am-5pm), excludes Saturday & Sunday and excludes Public Holidays in HolidayList is SUMMED. This is because On Hold time outside of 9-5, weekends and public holidays are not considered valid for measurement hence why we have also removed the same from the Actual Assigned Hours (decimal hours).

On Sheet2, I have tried to add a few situations such as a public holiday and some occasions where the on hold time starts before the assignment start time or ends after the assignment end time. But this sheet will basically be filled with on hold status periods of many incidents so it's important its only picking those where the incident number matches the number on Sheet1.

My understanding is that once I have this last missing formula sorted, I should be able to subtract the figure from the Actual Assigned Hours (decimal hours) to get the true amount of assignment decimal hours for the purposes of understanding whether an Assignment Group has passed or failed it's SLA by holding an incident too long on hold.

I appreciate this is a complex method to calculate, but unfortunately I am having to do it this way due to the constraints of the system exporting the data. These are the formats of the extracts and they cannot be changed, so I need to work with what I have I'm afraid.

Thank you very much in advance if anyone is able to help me with this. Please let me know if any clarifications are required.

Here are mini-sheets of each Sheet in my document. An upload can also be provided if required.

Constants:

Book1.xlsx
	A	B	C	D	E	F
1	DayStart	9:00:00 AM
2	DayEnd	5:00:00 PM
3	HolidayList	02/01/2023
4		07/04/2023
5		10/04/2023
6		01/05/2023
7		08/05/2023
8		29/05/2023
9		28/08/2023
10		25/12/2023
11		26/12/2023
Constants

Sheet1 (main data):

Book1.xlsx
	A	B	C	D	E	F	G	H	I
1	Incident Number	Assignment Start Date/Time	Assignment End Date/Time	Assigned Hours (Decimal hours)	Actual Assigned Hours (Decimal hours) Between 9am - 5pm Excluding Saturday & Sunday Excluding Public Holidays List (HolidayList)	Overlap Found	Total Overlap ("On Hold" time) (Decimal hours)	Overlap ("On Hold" time) (Decimal hours) Between 9am - 5pm Excluding Saturday & Sunday Excluding Public Holidays List (HolidayList)	Assigned Hours (Excluding "On Hold" time) (Decimal hours)
2	INC0000001	02/01/2023 9:00	03/01/2023 14:38	29.6391666666604000	5.6391666666604500	0	0.00000000000000		=E2-H2
3	INC0000001	03/01/2023 14:38	03/01/2023 16:01	1.3902777777984700	1.3902777777984700	1	0.02944444451714		=E2-H2
4	INC0000001	03/01/2023 16:01	03/01/2023 16:02	0.0191666666069068	0.0191666666069068	1	0.01916666660691		=E3-H3
5	INC0000001	03/01/2023 16:02	03/01/2023 18:34	2.5325000000302700	0.9513888889341620	1	1.95138888893416		=E4-H4
6	INC0000001	03/01/2023 18:34	04/01/2023 9:44	15.1605555555434000	0.7416666666395030	0	0.00000000000000		=E5-H5
7	INC0000001	04/01/2023 9:44	04/01/2023 10:38	0.9002777778077870	0.9002777778077870	1	0.64194444450550		=E6-H6
Sheet1

Cell Formulas
Range	Formula
D2:D7	D2	=IF(OR(B2="",C2=""),"",(C2-B2)*24)
E2:E7	E2	=IFERROR(IF(C2="","",((NETWORKDAYS.INTL(B2,C2,1,HolidayList)-1)*(DayEnd-DayStart)+IF(NETWORKDAYS.INTL(C2,C2,1,HolidayList),MEDIAN(MOD(C2,1),DayStart,DayEnd),DayEnd)-MEDIAN(NETWORKDAYS.INTL(B2,B2,1,HolidayList)*MOD(B2,1),DayStart,DayEnd)))*24,"")
F2:F7	F2	=SUM(IF(Sheet2!$A$3:$A$44=A2,IF(IF(C2<Sheet2!$D$3:$D$44,C2,Sheet2!$D$3:$D$44)-IF(B2>Sheet2!$C$3:$C$44,B2,Sheet2!$C$3:$C$44)>0,1)))
G2:G7	G2	=SUM(IF(Sheet2!$A$3:$A$44=A2,IF(IF(C2<Sheet2!$D$3:$D$44,C2,Sheet2!$D$3:$D$44)-IF(B2>Sheet2!$C$3:$C$44,B2,Sheet2!$C$3:$C$44)>0,(IF(C2<Sheet2!$D$3:$D$44,C2,Sheet2!$D$3:$D$44)-IF(B2>Sheet2!$C$3:$C$44,B2,Sheet2!$C$3:$C$44))*24)))
Press CTRL+SHIFT+ENTER to enter array formulas.

Named Ranges
Name	Refers To	Cells
DayEnd	=Constants!$B$2	E2:E7
DayStart	=Constants!$B$1	E2:E7
HolidayList	=Constants!$B$3:$B$11	E2:E7

Sheet2 (On Hold periods):

Book1.xlsx
	A	B	C	D	E	F
1	Number	Value	Start	End	Total ("On Hold" time) for period (Decimal hours)	Total ("On Hold" time) for period (Decimal hours) Between 9am - 5pm Excluding Saturday & Sunday Excluding Public Holidays List (HolidayList)
2	INC0000001	On Hold	02/01/2023 10:00	03/01/2023 15:00	29.000000000058200	6.000000000000000
3	INC0000001	On Hold	03/01/2023 16:00	03/01/2023 18:00	2.000000000058210	1.000000000058210
4	INC0000001	On Hold	04/01/2023 10:00	04/01/2023 14:00	4.000000000116420	4.000000000116420
5	INC0000001	On Hold	05/01/2023 5:00	05/01/2023 10:00	4.999999999883580	0.999999999941792
6	INC0000001	On Hold	06/01/2023 8:00	09/01/2023 11:00	75.000000000000000	10.000000000058200
Sheet2

Cell Formulas
Range	Formula
E2:E6	E2	=IF(OR(C2="",D2=""),"",(D2-C2)*24)
F2:F6	F2	=IFERROR(IF(D2="","",((NETWORKDAYS.INTL(C2,D2,1,HolidayList)-1)*(DayEnd-DayStart)+IF(NETWORKDAYS.INTL(D2,D2,1,HolidayList),MEDIAN(MOD(D2,1),DayStart,DayEnd),DayEnd)-MEDIAN(NETWORKDAYS.INTL(C2,C2,1,HolidayList)*MOD(C2,1),DayStart,DayEnd)))*24,"")
Press CTRL+SHIFT+ENTER to enter array formulas.

Named Ranges
Name	Refers To	Cells
DayEnd	=Constants!$B$2	F2:F6
DayStart	=Constants!$B$1	F2:F6
HolidayList	=Constants!$B$3:$B$11	F2:F6

 

[JoeC]

Just giving this a little bump. Any help would be appreciated many thanks.

 

[awoohaw]

I don't understand the difference between similar column on sheet2 v sheet1. I also do not understand the concept of overlap hours?

 

[JoeC]

Which specific columns do you not understand the difference between? Please can you give me the columns?

Overlap hours means that I'm trying to calculate the overlap in hours between the start date/time and end date/time on Sheet1 and the start date/time and end date/time on Sheet2. Sheet1 are all assignment group periods, Sheet2 are all on-hold status periods. The goal is to sum the amount of hours of on-hold time an incident had when it was with each assignment group. So first the date/time periods needs to be compared, and if the on-hold period falls within the assignment group period then the number of hours that fall within the period are to be summed.

But then further to that, non-work time, weekends and public holidays needs to be excluded.

 

[awoohaw]

by overlap, do you mean mutual time segments in two different time frames?

to me if I have two time frames: 1) 2:00 pm to 5:00 pm, and 2) 4:00 Pm to 8:00 ... there is an mutual time segment (overlap) of 1 hour: 4:00 pm to 5 pm.
If that is what you have please tell me where the start and end times for each time frame is.

if it is not what you have.. then please explain again.

 

[fjns]

Hi, see the linked file for a possible solution...

Unfortunately, Google Drive does not recognize (e.g. FILTER) or mishandles (e.g. LET) some new functions. Spreadsheets worked fine most of the time after downloading, but there have been times when Google Drive broke a table and it malfunctioned after downloading. That's why I now uploaded the table Bokk1.xlsx also packed. Fortunately, after downloading the ZIP file, the unpacked Book1.xlsx table will work correct with your Office 365.

The formulas used in the table:
=IF(F2=0,0,SUMIF(A$2:A2,A2,F$2:F2))
=IF(F2=0,0,SUM(F$2:F2))
=MAX(Sheet1!H:H)+1
=IF(ROW()>$A$2,"",INDEX(Sheet1!A$2:A$7,IFERROR(XMATCH(ROW()-1,Sheet1!H$2:H$7,1,-1),0)))
=IF(ROW()>$A$2,"",INDEX(Sheet1!B$2:B$7,IFERROR(XMATCH(ROW()-1,Sheet1!H$2:H$7,1,-1),0)))
=IF(ROW()>$A$2,"",INDEX(Sheet1!C$2:C$7,IFERROR(XMATCH(ROW()-1,Sheet1!H$2:H$7,1,-1),0)))
=LET(f,FILTER(Sheet2!$C$2:$C$6,Sheet2!$A$2:$A$6=B2),IF(ROW()>$A$2,"",INDEX(f,IF(AND(B2=B1,C2=C1,D2=D1),MATCH(E1,f,0)+1,COUNTIFS(Sheet2!A$2:A$6,B2,Sheet2!D$2:D$6,"<"&C2)+1))))
=LET(f,FILTER(Sheet2!$D$2:$D$6,Sheet2!$A$2:$A$6=B2),IF(ROW()>$A$2,"",INDEX(f,IF(AND(B2=B1,C2=C1,D2=D1),MATCH(F1,f,0)+1,COUNTIFS(Sheet2!A$2:A$6,B2,Sheet2!D$2:D$6,"<"&C2)+1))))
=IF(ROW()>$A$2,"",IF(C2>E2,C2,E2))
=IF(ROW()>$A$2,"",IF(D2<F2,D2,F2))
=IF(ROW()>$A$2,"",IF(OR(G2="",H2=""),"",(H2-G2)*24))
=IF(ROW()>$A$2,"",IFERROR(IF(H2="","",((NETWORKDAYS.INTL(G2,H2,1,HolidayList)-1)*(DayEnd-DayStart)+IF(NETWORKDAYS.INTL(H2,H2,1,HolidayList),MEDIAN(MOD(H2,1),DayStart,DayEnd),DayEnd)-MEDIAN(NETWORKDAYS.INTL(G2,G2,1,HolidayList)*MOD(G2,1),DayStart,DayEnd)))*24,""))

Book1.xlsx
Book1.zip