Calculating Incident Duration Across Non-Standard Hours

[Puni19]

Hi All,

Excel version – 2010

I’m trying to calculate ticket incident duration in minutes for issues that occur in an environment with non-standard business hours. Depending on the location the incident occurs the hours in the “business day” vary and need to accommodate the total duration of the incident relative to the hours of operation in each day.

As an example, an incident occurs in a shopping mall with hours of operation - Monday to Wednesday & Friday to Saturday 0900-1630; Thursday 0900-1800; Sunday 1000-1400

An incident is raised 12/04/2015 13:00 (Sunday) and closed 18/04/2015 9:30 (Saturday), manually calculating the duration of time is 2,430 minutes.

With the help of the forum we were able to determine the below formula, however, my issue now is how to translate the formula to accommodate different hours of operations. I can follow the majority of the formula, but am struggling with the values in the CHOOSE function and how the values relate to the over formula. I can see the CHOOSE expression in the first half of the formula contains the daily hours of operation from Monday to Sunday, however, come unstuck further into the formula.

Am seeking assistance to help create formulas for the remaining hours of operation in the attached spreadsheet. Cell reference H2 shows the formula I was provided for the above example. Could you please assist and provide formulas in H3 – H7

Excel 2010

	A	B	C	D	E	F	G	H
Hours of Operation
Monday to Wednesday & Friday to Saturday 0900-1630; Thursday 0900-1800; Sunday 1000-1400
Monday to Tuesday & Thursday to Friday 0900-1630; Wednesday 0900 – 1830; Saturday & Sunday not open
Monday to Friday 0900-1630; Saturday 1000-1300; Sunday not open
Monday to Wednesday and Saturday 0900-1630; Thursday to Friday 0900-1900; Sunday 1000-1600
Monday to Sunday 0800- 1800
Monday to Friday 0900-1630

<colgroup><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: center"]Start[/TD]
[TD="align: center"]Finish[/TD]
[TD="align: center"]Manual Calculation (min)[/TD]
[TD="align: center"]Hours of Operation Downtime (min)[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: center"]12/04/2015 13:00[/TD]
[TD="align: center"]18/04/2015 9:30[/TD]
[TD="align: center"]2430[/TD]
[TD="align: center"]2430[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: center"]12/04/2015 13:00[/TD]
[TD="align: center"]18/04/2015 9:30[/TD]
[TD="align: center"]2370[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: center"]12/04/2015 13:00[/TD]
[TD="align: center"]18/04/2015 9:30[/TD]
[TD="align: center"]2430[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: center"]12/04/2015 13:00[/TD]
[TD="align: center"]18/04/2015 9:30[/TD]
[TD="align: center"]3360[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: center"]12/04/2015 13:00[/TD]
[TD="align: center"]18/04/2015 9:30[/TD]
[TD="align: center"]3690[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: center"]12/04/2015 13:00[/TD]
[TD="align: center"]18/04/2015 9:30[/TD]
[TD="align: center"]2250[/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: #DAE7F5"]H2[/TH]
[TD="align: left"]=(SUMPRODUCT(NETWORKDAYS.INTL(E2,F2,SUBSTITUTE("1111111",1,0,{1,2,3,4,5,6,7})),{7.5,7.5,7.5,9,7.5,7.5,4})-MEDIAN(0,CHOOSE(WEEKDAY(E2,2),7.5,7.5,7.5,9,7.5,7.5,4),MOD(E2,2)*24-CHOOSE(WEEKDAY(E2,2),9,9,9,9,9,9,10))-MEDIAN(0,CHOOSE(WEEKDAY(F2,2),7.5,7.5,7.5,9,7.5,7.5,4),CHOOSE(WEEKDAY(F2,2),16.5,16.5,16.5,18,16.5,16.5,14)-MOD(F2,2)*24))*60[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]




Thanks,

 

[barry houdini]

Where you have 9,9,9,9,9,9,10 in the 3rd CHOOSE function those represent the start times of each day (Monday to Sunday) and in the 5th CHOOSE function you have the end times for each day (in "24 hour clock"), i.e. 16.5,16.5,16.5,18,16.5,16.5,14

If the mall isn't open on any day use zeroes, so for row 3 you can use this formula

=(SUMPRODUCT(NETWORKDAYS.INTL(E3,F3,SUBSTITUTE("1111111",1,0,{1,2,3,4,5,6,7})),{7.5,7.5,9.5,7.5,7.5,0,0})-MEDIAN(0,CHOOSE(WEEKDAY(E3,2),7.5,7.5,9.5,7.5,7.5,0,0),MOD(E3,2)*24-CHOOSE(WEEKDAY(E3,2),9,9,9,9,9,0,0))-MEDIAN(0,CHOOSE(WEEKDAY(F3,2),7.5,7.5,9.5,7.5,7.5,0,0),CHOOSE(WEEKDAY(F3,2),16.5,16.5,18.5,16.5,16.5,0,0)-MOD(F3,2)*24))*60

Adjust in a similar way for the others

 

[Puni19]

Hi Barry Houdini,

Many thanks for your help, I see the logic between the expressions. Playing around with the formula and applying to different incident duration the calculated results are fairly inconsistent. In the shopping mall example I gave the incident spanned 7 days - my attempt was to show the need to calculate duration across varied business hours, however, not sure if the formula accounts for incidents that may only be a matter of a few hours in duration.

Using your provided example from row three I applied it to sites with the same hours of operation but whose incidents were of different durations and the results are incorrect compared to manually calculating the time.

Are you able to assist please and see if there is an error in the formula itself? It appears if the incident is opened and closed in the same day, or within a day it is incorrect, but correct if the incident is across a few days -

Excel 2010
	A	B	C	E	F	G	H	I
1			Hours of Operation	Start	Finish	Manual Calculation (mins)	Hours of Operation Downtime (min)
2			Monday to Tuesday & Thursday to Friday 0900-1630; Wednesday 0900 1830; Saturday & Sunday not open	21/04/2015 9:00	21/04/2015 16:30	450	0	Incorrect
3			Monday to Tuesday & Thursday to Friday 0900-1630; Wednesday 0900 1830; Saturday & Sunday not open	21/04/2015 9:00	22/04/2015 10:00	510	60	Incorrect
4			Monday to Tuesday & Thursday to Friday 0900-1630; Wednesday 0900 1830; Saturday & Sunday not open	21/04/2015 9:00	21/04/2015 10:00	60	0	Incorrect
5			Monday to Tuesday & Thursday to Friday 0900-1630; Wednesday 0900 1830; Saturday & Sunday not open	20/04/2015 9:00	22/04/2015 10:00	960	960	Correct
Sheet2

Cell Formulas
Range	Formula
H2	=(SUMPRODUCT(NETWORKDAYS.INTL(E2,F2,SUBSTITUTE("1111111",1,0,{1,2,3,4,5,6,7})),{7.5,7.5,9.5,7.5,7.5,0,0})-MEDIAN(0,CHOOSE(WEEKDAY(E2,2),7.5,7.5,9.5,7.5,7.5,0,0),MOD(E2,2)*24-CHOOSE(WEEKDAY(E2,2),9,9,9,9,9,0,0))-MEDIAN(0,CHOOSE(WEEKDAY(F2,2),7.5,7.5,9.5,7.5,7.5,0,0),CHOOSE(WEEKDAY(F2,2),16.5,16.5,18.5,16.5,16.5,0,0)-MOD(F2,2)*24))*60
H3	=(SUMPRODUCT(NETWORKDAYS.INTL(E3,F3,SUBSTITUTE("1111111",1,0,{1,2,3,4,5,6,7})),{7.5,7.5,9.5,7.5,7.5,0,0})-MEDIAN(0,CHOOSE(WEEKDAY(E3,2),7.5,7.5,9.5,7.5,7.5,0,0),MOD(E3,2)*24-CHOOSE(WEEKDAY(E3,2),9,9,9,9,9,0,0))-MEDIAN(0,CHOOSE(WEEKDAY(F3,2),7.5,7.5,9.5,7.5,7.5,0,0),CHOOSE(WEEKDAY(F3,2),16.5,16.5,18.5,16.5,16.5,0,0)-MOD(F3,2)*24))*60
H4	=(SUMPRODUCT(NETWORKDAYS.INTL(E4,F4,SUBSTITUTE("1111111",1,0,{1,2,3,4,5,6,7})),{7.5,7.5,9.5,7.5,7.5,0,0})-MEDIAN(0,CHOOSE(WEEKDAY(E4,2),7.5,7.5,9.5,7.5,7.5,0,0),MOD(E4,2)*24-CHOOSE(WEEKDAY(E4,2),9,9,9,9,9,0,0))-MEDIAN(0,CHOOSE(WEEKDAY(F4,2),7.5,7.5,9.5,7.5,7.5,0,0),CHOOSE(WEEKDAY(F4,2),16.5,16.5,18.5,16.5,16.5,0,0)-MOD(F4,2)*24))*60
H5	=(SUMPRODUCT(NETWORKDAYS.INTL(E5,F5,SUBSTITUTE("1111111",1,0,{1,2,3,4,5,6,7})),{7.5,7.5,9.5,7.5,7.5,0,0})-MEDIAN(0,CHOOSE(WEEKDAY(E5,2),7.5,7.5,9.5,7.5,7.5,0,0),MOD(E5,2)*24-CHOOSE(WEEKDAY(E5,2),9,9,9,9,9,0,0))-MEDIAN(0,CHOOSE(WEEKDAY(F5,2),7.5,7.5,9.5,7.5,7.5,0,0),CHOOSE(WEEKDAY(F5,2),16.5,16.5,18.5,16.5,16.5,0,0)-MOD(F5,2)*24))*60

Thanks,

 

[barry houdini]

The formulas are designed to work with any time period, either on the same day or over multiple days.....but there was an error in the formula you posted in your first post (I know that wasn't your fault) and I foolishly propagated that same error in to my reply

Both the MOD functions are supposed to have a divisor of 1 (not 2) so this version should work for you

=(SUMPRODUCT(NETWORKDAYS.INTL(E2,F2,SUBSTITUTE("1111111",1,0,{1,2,3,4,5,6,7})),{7.5,7.5,9.5,7.5,7.5,0,0})-MEDIAN(0,CHOOSE(WEEKDAY(E2,2),7.5,7.5,9.5,7.5,7.5,0,0),MOD(E2,1)*24-CHOOSE(WEEKDAY(E2,2),9,9,9,9,9,0,0))-MEDIAN(0,CHOOSE(WEEKDAY(F2,2),7.5,7.5,9.5,7.5,7.5,0,0),CHOOSE(WEEKDAY(F2,2),16.5,16.5,18.5,16.5,16.5,0,0)-MOD(F2,1)*24))*60

 

[Puni19]

Hi.

Apologies, it was my error providing the incorrect formula to begin with.

Your adjusted formula is awesome, provides the detail I require! Many thanks for your prompt and helpful assistance, much appreciated.

Thanks,