Calculating duration based on NETWORKDAYS + working time

[dellzy]

Hello experts,

Usually we use this formula to calculate duration that one uses to handle a case in ticketing/helpdesk/servicedesk system :-
=NETWORKDAYS(date1,date2,[holidays])

But how about if we want to significantly count the SLA days start from the working hours as well? For example, if working hours starts from 9.00am til 5.00pm, if there's a case assigned at 6.00pm, the SLA days should start from the next business day.otherwise, it won't be a fair calculation for the staff as we don't provide 24hours service at the moment.

Any particular modification on the existing formula to cater for this,please?

Please advice. Thank you.

 

[DRSteele]

What you're after is a way to calculate NetworkHours in a similar way to function NETWORKDAYS. Unfortunataley, no such function yet exists. The alternative algorithm is complex, and I've dealt with it before. Peruse this thread and its link and determine whether or not any of this helps: http://www.mrexcel.com/forum/excel-questions/905060-tat-minutes-including-weekend.html#post4356346

Also, please go here, sign up and vote for the creation of this kind of function. If enough votes accumulate, Microsoft might make it a reality. Create NETWORKHOURS as a new function

 

[dellzy]

Thank you very much for the leads, DRSteele.

Will try the formula you've referred me to and will update if it works on my scenario. I guess it takes time for me to digest as it does look very complicated.

 

[dellzy]

Hi DRSteele,

I've tried your formula as posted earlier (link). But I also have '0' result just like the earlier asker. And that post seem to be ignored after that. Anythg I did wrong?

Thank you for your kind assistance.

DZ

 

[jorismoerings]

Hi DZ,

try this:

	A	B
Start Time
End Time
Diff B1-A1

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]09:00:00[/TD]

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

[TD="align: right"]17:00:00[/TD]

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

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

[TD="align: center"]5[/TD]
[TD="align: center"]6-1-2016 09:35[/TD]
[TD="align: center"]7-1-2016 09:35[/TD]

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

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

[TD="align: right"]08:00:00[/TD]

</tbody>

Sheet1 (2)​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B7[/TH]
[TD="align: left"]=(NETWORKDAYS(A5,B5)-1)*($B$2-$B$1)+IF(NETWORKDAYS(B5,B5),MEDIAN(MOD(B5,1),$B$2,$B$1),$B$2)-MEDIAN(NETWORKDAYS(A5,A5)*MOD(A5,1),$B$2,$B$1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[dellzy]

Hi jorismoerings

Your formula works! Tqvm,jorismoerings! You're my saviour!
DZ

 

[dellzy]

Hi DZ,

try this:

	A	B
Start Time
End Time
Diff B1-A1

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]09:00:00[/TD]

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

[TD="align: right"]17:00:00[/TD]

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

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

[TD="align: center"]5[/TD]
[TD="align: center"]6-1-2016 09:35[/TD]
[TD="align: center"]7-1-2016 09:35[/TD]

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

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

[TD="align: right"]08:00:00[/TD]

</tbody>

Sheet1 (2)​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B7[/TH]
[TD="align: left"]=(NETWORKDAYS(A5,B5)-1)*($B$2-$B$1)+IF(NETWORKDAYS(B5,B5),MEDIAN(MOD(B5,1),$B$2,$B$1),$B$2)-MEDIAN(NETWORKDAYS(A5,A5)*MOD(A5,1),$B$2,$B$1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Oh,i forgot one thing.how about if I want to subtract the holidays range of dates,please? Where in the formula,I can insert the namedrange,please?

Thank you in advance.

DZ

 

[jorismoerings]

If you want to add the holidays, creates a range of dates you want to exclude, name it Holidays and enter it like this:

	A	B
Start Time
End Time
Diff A5 - B5

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]09:00:00[/TD]

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

[TD="align: right"]17:00:00[/TD]

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

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

[TD="align: center"]5[/TD]
[TD="align: center"]6-1-2016 09:35[/TD]
[TD="align: center"]7-1-2016 09:35[/TD]

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

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

[TD="align: right"]08:00:00[/TD]

</tbody>

Sheet1 (2)​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B7[/TH]
[TD="align: left"]=(NETWORKDAYS(A5,B5,holidays)-1)*($B$2-$B$1)+IF(NETWORKDAYS(B5,B5,holidays),MEDIAN(MOD(B5,1),$B$2,$B$1),$B$2)-MEDIAN(NETWORKDAYS(A5,A5,holidays)*MOD(A5,1),$B$2,$B$1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Workbook Defined Names[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Name[/TH]
[TH="align: left"]Refers To[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]holidays[/TH]
[TD="align: left"]=Sheet1!$A$7:$A$14[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Glad you got there in the end and i could help.

 

[dellzy]

Yes,it works just like how I have wanted it. Tqvm,again,jorismoerings!

 

[jorismoerings]

Glad i could help but do agree with DRSteele; it would be so much easier if there was a standard excel function for everything and by judging the number of OP's already asked this question (in some many different varieties) there's enough motivation for Microsoft to do so. I didn't saw it in Excel 2016 so let's wait and see. But for now the formula works.