Calculate worked hours based upon criteria

Skybluekid

Well-known Member
Joined
Apr 17, 2012
Messages
829
Hi All,


I have a sheet where it records a time when one of our trucks goes into a dealer. It also records the time the trucks leaves the dealer. I would like to workout how long the vehicle has been stood in the dealer. That is easy to do, but in order to be accurate, I would like the dealers opening hours to be considered.

For example, The truck enters a dealer, and we have a time stamp of 5/8/2019 08:00. The truck leaves on the 12/8/19 12:00. The Dealer opening hours are 08:00 - 23:00 Mon-Fri 08:00 - 12:00 Saturday Closed on Sunday.

So in the above scenario, the answer would be 75hrs


Thanks in Advance
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

jorismoerings

Well-known Member
Joined
Jul 4, 2014
Messages
1,284
Hi,

I can't match your answer but i assume you've made a calculation error.
Check my table and formula and try if it suits your need:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">In</td><td style=";">Out</td><td style=";">Total</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">5-8-2019 08:00</td><td style="text-align: right;;">12-8-2019 12:00</td><td style="text-align: right;;">83:00:00</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">05-08-2019</td><td style=";">Monday</td><td style="text-align: right;;">08:00</td><td style="text-align: right;;">23:00</td><td style="text-align: right;;">15:00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">06-08-2019</td><td style=";">Tuesday</td><td style="text-align: right;;">08:00</td><td style="text-align: right;;">23:00</td><td style="text-align: right;;">15:00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;">07-08-2019</td><td style=";">Wednesday</td><td style="text-align: right;;">08:00</td><td style="text-align: right;;">23:00</td><td style="text-align: right;;">15:00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;">08-08-2019</td><td style=";">Thursday</td><td style="text-align: right;;">08:00</td><td style="text-align: right;;">23:00</td><td style="text-align: right;;">15:00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;">09-08-2019</td><td style=";">Friday</td><td style="text-align: right;;">08:00</td><td style="text-align: right;;">23:00</td><td style="text-align: right;;">15:00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;">10-08-2019</td><td style=";">Saturday</td><td style="text-align: right;;">08:00</td><td style="text-align: right;;">12:00</td><td style="text-align: right;;">04:00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;">11-08-2019</td><td style=";">Sunday</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;;">12-08-2019</td><td style=";">Monday</td><td style="text-align: right;;">08:00</td><td style="text-align: right;;">12:00</td><td style="text-align: right;;">04:00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">83:00:00</td></tr></tbody></table><p style="width:3,6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet3</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C2</th><td style="text-align:left">=(<font color="Blue">NETWORKDAYS.INTL(<font color="Red">A2,B2,1</font>)-1</font>)*(<font color="Blue">"23:00"-"08:00"</font>)+IF(<font color="Blue">NETWORKDAYS.INTL(<font color="Red">B2,B2,1</font>),MEDIAN(<font color="Red">MOD(<font color="Green">B2,1</font>),"23:00","08:00"</font>),"23:00"</font>)-MEDIAN(<font color="Blue">NETWORKDAYS.INTL(<font color="Red">A2,A2,1</font>)*MOD(<font color="Red">A2,1</font>),"23:00","08:00"</font>)+(<font color="Blue">NETWORKDAYS.INTL(<font color="Red">A2,B2,"1111101"</font>)-1</font>)*(<font color="Blue">"12:00"-"08:00"</font>)+IF(<font color="Blue">NETWORKDAYS.INTL(<font color="Red">B2,B2,"1111101"</font>),MEDIAN(<font color="Red">MOD(<font color="Green">B2,1</font>),"12:00","08:00"</font>),"12:00"</font>)-MEDIAN(<font color="Blue">NETWORKDAYS.INTL(<font color="Red">A2,A2,"1111101"</font>)*MOD(<font color="Red">A2,1</font>),"12:00","08:00"</font>)</td></tr></tbody></table></td></tr></table><br />
 

Sam_D_Ben

Active Member
Joined
Oct 17, 2012
Messages
379
Hello Jorismoerings, I was wondering why i am getting a different figure with your formula.

<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(102,0,0)"><colgroup><col width="25px" style="background-color: rgb(255,255,255)" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(255,255,255);text-align: center;color: rgb(102,0,0)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: rgb(102,0,0);text-align: center;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">In</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">Out</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">Total</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td></tr><tr ><td style="color: rgb(102,0,0);text-align: center;">2</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">5/8/2019 8:00</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">12/8/2019 12:00</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">100.79</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td></tr><tr ><td style="color: rgb(102,0,0);text-align: center;">3</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td></tr><tr ><td style="color: rgb(102,0,0);text-align: center;">4</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">8-May-19</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">Monday</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">8:00</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">23:00</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">15:00</td></tr><tr ><td style="color: rgb(102,0,0);text-align: center;">5</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">8-Jun-19</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">Tuesday</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">8:00</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">23:00</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">15:00</td></tr><tr ><td style="color: rgb(102,0,0);text-align: center;">6</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">8-Jul-19</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">Wednesday</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">8:00</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">23:00</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">15:00</td></tr><tr ><td style="color: rgb(102,0,0);text-align: center;">7</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">8-Aug-19</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">Thursday</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">8:00</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">23:00</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">15:00</td></tr><tr ><td style="color: rgb(102,0,0);text-align: center;">8</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">8-Sep-19</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">Friday</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">8:00</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">23:00</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">15:00</td></tr><tr ><td style="color: rgb(102,0,0);text-align: center;">9</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">8-Oct-19</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">Saturday</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">8:00</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">12:00</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">4:00</td></tr><tr ><td style="color: rgb(102,0,0);text-align: center;">10</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">8-Nov-19</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">Sunday</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td></tr><tr ><td style="color: rgb(102,0,0);text-align: center;">11</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">8-Dec-19</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">Monday</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">8:00</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">12:00</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">4:00</td></tr><tr ><td style="color: rgb(102,0,0);text-align: center;">12</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">83:00:00</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(102,0,0);border-top:none;text-align: center;background-color: rgb(255,255,255);color: rgb(102,0,0)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(102,0,0)"><thead><tr style=" background-color: rgb(255,255,255);color: rgb(102,0,0)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(255,255,255);color: rgb(102,0,0)">C2</th><td style="text-align:left">=(<font color="Blue">NETWORKDAYS.INTL(<font color="Red">A2,B2,1</font>)-1</font>)*(<font color="Blue">"23:00"-"08:00"</font>)+IF(<font color="Blue">NETWORKDAYS.INTL(<font color="Red">B2,B2,1</font>),MEDIAN(<font color="Red">MOD(<font color="Green">B2,1</font>),"23:00","08:00"</font>),"23:00"</font>)-MEDIAN(<font color="Blue">NETWORKDAYS.INTL(<font color="Red">A2,A2,1</font>)*MOD(<font color="Red">A2,1</font>),"23:00","08:00"</font>)+(<font color="Blue">NETWORKDAYS.INTL(<font color="Red">A2,B2,"1111101"</font>)-1</font>)*(<font color="Blue">"12:00"-"08:00"</font>)+IF(<font color="Blue">NETWORKDAYS.INTL(<font color="Red">B2,B2,"1111101"</font>),MEDIAN(<font color="Red">MOD(<font color="Green">B2,1</font>),"12:00","08:00"</font>),"12:00"</font>)-MEDIAN(<font color="Blue">NETWORKDAYS.INTL(<font color="Red">A2,A2,"1111101"</font>)*MOD(<font color="Red">A2,1</font>),"12:00","08:00"</font>)</td></tr></tbody></table></td></tr></table><br />
 

jorismoerings

Well-known Member
Joined
Jul 4, 2014
Messages
1,284
@Sam_D_Ben,

Hi, i'm getting the same result as you howeverthe devil is in the details.
The OP ask a question regarding a timeframe and from his/her originating country i deducted the date/time notation was UK.
5/8/2019 08:00. The truck leaves on the 12/8/19 12:00

doesn't mean may 8th tll december 8th but August 5th till August 12th.

When entered, these are the results:
<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">In</td><td style=";">Out</td><td style=";">Total</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">8 May 2019 08:00</td><td style="text-align: right;;">8 December 2019 12:00</td><td style="text-align: right;;">100,7916667</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">5 August 2019 08:00</td><td style="text-align: right;;">12 August 2019 12:00</td><td style="text-align: right;;">3,458333333</td></tr></tbody></table><p style="width:3,6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C2</th><td style="text-align:left">=(<font color="Blue">NETWORKDAYS.INTL(<font color="Red">A2,B2,1</font>)-1</font>)*(<font color="Blue">"23:00"-"08:00"</font>)+IF(<font color="Blue">NETWORKDAYS.INTL(<font color="Red">B2,B2,1</font>),MEDIAN(<font color="Red">MOD(<font color="Green">B2,1</font>),"23:00","08:00"</font>),"23:00"</font>)-MEDIAN(<font color="Blue">NETWORKDAYS.INTL(<font color="Red">A2,A2,1</font>)*MOD(<font color="Red">A2,1</font>),"23:00","08:00"</font>)+(<font color="Blue">NETWORKDAYS.INTL(<font color="Red">A2,B2,"1111101"</font>)-1</font>)*(<font color="Blue">"12:00"-"08:00"</font>)+IF(<font color="Blue">NETWORKDAYS.INTL(<font color="Red">B2,B2,"1111101"</font>),MEDIAN(<font color="Red">MOD(<font color="Green">B2,1</font>),"12:00","08:00"</font>),"12:00"</font>)-MEDIAN(<font color="Blue">NETWORKDAYS.INTL(<font color="Red">A2,A2,"1111101"</font>)*MOD(<font color="Red">A2,1</font>),"12:00","08:00"</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C3</th><td style="text-align:left">=(<font color="Blue">NETWORKDAYS.INTL(<font color="Red">A3,B3,1</font>)-1</font>)*(<font color="Blue">"23:00"-"08:00"</font>)+IF(<font color="Blue">NETWORKDAYS.INTL(<font color="Red">B3,B3,1</font>),MEDIAN(<font color="Red">MOD(<font color="Green">B3,1</font>),"23:00","08:00"</font>),"23:00"</font>)-MEDIAN(<font color="Blue">NETWORKDAYS.INTL(<font color="Red">A3,A3,1</font>)*MOD(<font color="Red">A3,1</font>),"23:00","08:00"</font>)+(<font color="Blue">NETWORKDAYS.INTL(<font color="Red">A3,B3,"1111101"</font>)-1</font>)*(<font color="Blue">"12:00"-"08:00"</font>)+IF(<font color="Blue">NETWORKDAYS.INTL(<font color="Red">B3,B3,"1111101"</font>),MEDIAN(<font color="Red">MOD(<font color="Green">B3,1</font>),"12:00","08:00"</font>),"12:00"</font>)-MEDIAN(<font color="Blue">NETWORKDAYS.INTL(<font color="Red">A3,A3,"1111101"</font>)*MOD(<font color="Red">A3,1</font>),"12:00","08:00"</font>)</td></tr></tbody></table></td></tr></table><br />
 

Watch MrExcel Video

Forum statistics

Threads
1,108,768
Messages
5,524,787
Members
409,600
Latest member
Dunnowhatfor

This Week's Hot Topics

Top