Formula to calculate total processing time excluding weekends

Gaurangg

Board Regular
Joined
Aug 6, 2015
Messages
125
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.
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,619
Office Version
  1. 2007
Platform
  1. Windows
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>
 

Watch MrExcel Video

Forum statistics

Threads
1,128,128
Messages
5,628,862
Members
416,345
Latest member
sayad

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top