Formula to calculate total processing time excluding weekends

[Gaurangg]

Dear Folks,


I am stuck in a situation where I would like to calculate the total processing hours of a request. Of course I have request receive time and request processed time. Both time are in dd/mm/yy HH:mm:ss format. I have tried many combination of formula to calculate total working hours from both the times.


The issue is, requests can be received on any day of the week, however they need to be worked on weekdays only. We work round the clock shifts starting at 9:00am and offs on Saturday and Sunday. Hence And Night shift agent can submit his last request after 12:00am of Saturday (which is actually night shift of Friday). But when I tried to calculate total processing time excluding weekends, formula could not get the process time and throws the error as the weekend date is excluded by formula. Below I am trying to explain my logical condition of a request.


It will be really great if anyone can help me how to calculate the hours:


Conditions:


Request Received Time Request completed Time Total processing Hours (would be)


1) 26-Jul-19 18:35:23 -(Fri) 29-Jul-19 21:30:05 -(Mon) 17:54:42
2) 26-Jul-19 18:35:23 -(Fri) 27-Jul-19 01:30:05 -(Sat) 06:54:42


Logic for 1) :-


26-Jul-19 18:35:23 - 27-Jul-19 00:00:00 = 05:24:37
29-Jul-19 09:00:00 - 29-Jul-19 21:30:05 = 12:30:05


= Total 17:54:42

Where in 2) scenario it was worked on same day.

 

[DanteAmor]

I propose the following alternative.
In column A, start of time.
In the B, end of time, it must be 23:59:59 since 24:00 does not exist. (the second 00:00:01 is recovered later).
In the C and D dates.
In E and F they are Auxiliary columns.
And in G the result.

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:98.85px;" /><col style="width:98.85px;" /><col style="width:202.46px;" /><col style="width:193.9px;" /><col style="width:194.85px;" /><col style="width:192px;" /><col style="width:124.51px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffff00; font-weight:bold; font-size:10pt; text-align:center; ">Starting</td><td style="background-color:#ffff00; font-weight:bold; font-size:10pt; text-align:center; ">Hora final</td><td style="background-color:#c2d69a; font-weight:bold; font-size:10pt; text-align:center; ">Initial Date</td><td style="background-color:#fac090; font-weight:bold; font-size:10pt; text-align:center; ">Final date</td><td style="background-color:#fac090; font-weight:bold; font-size:10pt; text-align:center; ">Aux 1</td><td style="background-color:#fac090; font-weight:bold; font-size:10pt; text-align:center; ">Aux 2</td><td style="background-color:#95b3d7; font-weight:bold; font-size:10pt; text-align:center; ">Result</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-size:10pt; text-align:right; ">09:00:00 a.m.</td><td style="font-size:10pt; text-align:right; ">11:59:59 p.m.</td><td style="font-size:10pt; text-align:right; ">26/07/2019 06:35:23 p.m.</td><td style="font-size:10pt; text-align:right; ">29/07/2019 09:30:05 p.m.</td><td style="font-size:10pt; text-align:right; ">29/07/2019 09:30:05 p.m.</td><td style="font-size:10pt; text-align:right; ">29/07/2019 09:30:05 p.m.</td><td style="font-size:10pt; text-align:right; ">17:54:42</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-size:10pt; text-align:right; ">09:00:00 a.m.</td><td style="font-size:10pt; text-align:right; ">11:59:59 p.m.</td><td style="font-size:10pt; text-align:right; ">26/07/2019 06:35:23 p.m.</td><td style="font-size:10pt; text-align:right; ">27/07/2019 01:30:05 a.m.</td><td style="font-size:10pt; text-align:right; ">29/07/2019 01:30:05 a.m.</td><td style="font-size:10pt; text-align:right; ">29/07/2019 10:30:05 a.m.</td><td style="font-size:10pt; text-align:right; ">6:54:42</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="font-size:10pt; text-align:right; ">09:00:00 a.m.</td><td style="font-size:10pt; text-align:right; ">11:59:59 p.m.</td><td style="font-size:10pt; text-align:right; ">26/07/2019 06:35:23 p.m.</td><td style="font-size:10pt; text-align:right; ">26/07/2019 10:00:00 p.m.</td><td style="font-size:10pt; text-align:right; ">26/07/2019 10:00:00 p.m.</td><td style="font-size:10pt; text-align:right; ">26/07/2019 10:00:00 p.m.</td><td style="font-size:10pt; text-align:right; ">3:24:37</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="font-size:10pt; text-align:right; ">09:00:00 a.m.</td><td style="font-size:10pt; text-align:right; ">11:59:59 p.m.</td><td style="font-size:10pt; text-align:right; ">22/07/2019 09:00:00 a.m.</td><td style="font-size:10pt; text-align:right; ">27/07/2019 12:00:00 a.m.</td><td style="font-size:10pt; text-align:right; ">29/07/2019 12:00:00 a.m.</td><td style="font-size:10pt; text-align:right; ">29/07/2019 09:00:00 a.m.</td><td style="font-size:10pt; text-align:right; ">75:00:00</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Formeln der Tabelle</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Zelle</td><td >Formel</td></tr><tr><td >E2</td><td >=IF(WEEKDAY(D2,2)=6,D2+2,IF(WEEKDAY(D2,2)=7,D2+1,D2))</td></tr><tr><td >F2</td><td >=E2+IF(OR(WEEKDAY(D2,2)=6,WEEKDAY(D2,2)=7),A2,0)</td></tr><tr><td >G2</td><td >=(NETWORKDAYS(C2,F2) - 2) * (B2+"00:00:01"-A2) + B2 + "00:00:01" - MOD(C2,1) + MOD(F2,1) - A2</td></tr></table></td></tr></table>