# Formula to calculate total processing time excluding weekends

#### Gaurangg

##### Board Regular
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.

### Excel Facts

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

#### DanteAmor

##### Well-known Member
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>

Replies
1
Views
279
Replies
5
Views
80
Replies
7
Views
391
Replies
31
Views
515
Replies
9
Views
660

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

### 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.

### Which adblocker are you using?

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

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