Spreadsheet: 867784d1714575246-network-days-formula-not-handling-cases-when-start-date-is-weekend-calculating-time-duration-in-between-working-hours
Sheet: Sheet3

 | Business Hours Per Account |  |  |  |  |  |  |  | Bank Holidays |  |  | Data Table |  |  |  | Network Days |  | Start |  |  |  |  |  |  | End
 | Account | Day | Day | Workday | Start | End | Hours |  | Holiday | Date |  | Account | Open | Resolution |  | Days | FORMULA | 08:00:00 | Type | Priority | Target | Paused | PauseTime | PauseMinutes | 18:00:00
 | General | 2 | Monday | 0 | 08:00:00 | 18:00:00 |  |  | New Year's Day | 2021-01-01 00:00:00 |  | Account 1 | 2021-06-08 15:29:05.750000 | 2021-06-08 15:30:27.860000 |  |  | =IF(AND($N4>0,$O4>0),IF(INT($N4)-INT($O4)=0,MAX(0,MIN($T$3,ROUND(MOD($O4,1),6))-MAX($S$3,ROUND(MOD($N4,1),6))),MAX(0,MIN($T$3,ROUND(MOD($O4,1),6))-$S$3)+($T$3-MAX(0,MAX($S$3,ROUND(MOD($N4,1),6))))+(INT($O4)-INT($N4)>1)*($T$3-$S$3)*NETWORKDAYS(INT($N4)+1,INT($O4)-1,$K$4:$K$11)),IF(INT($N4)-INT(NOW())=0,MAX(0,MIN($T$3,ROUND(MOD(NOW(),1),6))-MAX($S$3,ROUND(MOD($N4,1),6))),MAX(0,MIN($T$3,ROUND(MOD(NOW(),1),6))-$S$3)+($T$3-MAX(0,MAX($S$3,ROUND(MOD($N4,1),6))))+(INT(NOW())-INT($N4)>1)*($T$3-$S$3)*NETWORKDAYS(INT($N4)+1,INT(NOW())-1,$K$4:$K$11))) |  | Incident | P3 | 2021-05-26 03:45:00 | False | 2021-05-24 08:07:29.547000 | 3033
 | General | 3 | Tuesday | 0 | 08:00:00 | 18:00:00 |  |  | Good Friday | 2021-04-02 00:00:00 |  | Account 1 | 2021-06-09 14:11:37.350000 |  |  |  |  |  | Incident | P3 | 2021-06-01 10:40:00 | False | 2021-06-04 07:22:51.013000 | 1420
 | General | 4 | Wednesday | 0 | 08:00:00 | 18:00:00 |  |  | Easter Monday | 2021-04-05 00:00:00 |  | Account 1 | 2021-06-09 15:20:29.733000 | 2021-06-09 15:23:30.437000 |  |  |  |  | Presales | P4 | 2021-06-18 17:00:00 | True | 2021-05-31 21:33:33.100000
 | General | 5 | Thursday | 0 | 08:00:00 | 18:00:00 |  |  | Early May Bank Holiday | 2021-05-03 00:00:00 |  | Account 1 | 2021-06-15 13:35:41.633000 | 2021-06-15 14:36:20.800000 |  |  |  |  | Incident | P3 | 2021-06-03 11:00:00 | True | 2021-05-31 21:38:31.237000
 | General | 6 | Friday | 0 | 08:00:00 | 18:00:00 |  |  | Spring Bank Holiday | 2021-05-31 00:00:00 |  | Account 1 | 2021-06-16 16:37:51.260000 | 2021-06-16 16:51:17.593000 |  |  |  |  | Problem | P3 | 2021-06-08 08:33:00 | False | 2021-05-31 21:42:56.440000 | 693
 | General | 7 | Saturday | 1 | Closed | Closed |  |  | Summer Bank Holiday | 2021-08-30 00:00:00 |  | Account 1 | 2021-06-27 07:48:30 | 2021-06-29 09:09:11 |  |  |  |  | Incident | P4 | 2021-06-10 17:00:00 | True | 2021-05-31 21:48:05.187000
 | General | 1 | Sunday | 1 | Closed | Closed |  |  | Christmas Day | 2021-12-27 00:00:00 |  | Account 1 | 2021-06-21 17:08:53.477000 | 2021-06-28 16:45:39.477000 |  |  |  |  | Incident | P3 | 2021-06-11 12:34:00 | False | 2021-05-31 21:56:55.857000 | 2734
 | Account 1 | 2 | Monday | 0 | 08:00:00 | 20:00:00 |  |  | Boxing Day | 2021-12-28 00:00:00 |  | Account 2 | 2021-06-22 09:44:35.017000 |  |  |  |  |  | Incident | P4 | 2021-06-25 14:50:00 | False | 2021-05-31 22:02:47.543000 | 2870
 | Account 1 | 3 | Tuesday | 0 | 08:00:00 | 20:00:00 |  |  |  |  |  | Account 2 | 2021-06-23 08:09:49.177000 | 2021-06-24 10:04:47.430000 |  |  |  |  | Problem | P2 | 2021-06-03 07:35:00 | False | 2021-05-31 22:11:20.460000 | 35
 | Account 1 | 4 | Wednesday | 0 | 08:00:00 | 20:00:00 |  |  |  |  |  | Account 2 | 2021-06-23 14:19:48.333000 | 2021-06-23 14:38:30.360000 |  |  |  |  | Problem | P3 | 2021-06-15 12:22:00 | True | 2021-06-14 14:54:08.923000 | 4882
 | Account 1 | 5 | Thursday | 0 | 08:00:00 | 20:00:00 |  |  |  |  |  | Account 2 | 2021-06-23 16:26:43.427000 |  |  |  |  |  | Incident | P4 | 2021-06-10 17:00:00 | True | 2021-05-31 22:25:22.927000
 | Account 1 | 6 | Friday | 0 | 08:00:00 | 20:00:00 |  |  |  |  |  | Account 2 | 2021-06-23 16:37:11.813000 |  |  |  |  |  | Incident | P3 | 2021-06-16 14:38:00 | False | 2021-05-31 22:30:25.737000 | 5618
 | Account 1 | 7 | Saturday | 1 | Closed | Closed |  |  |  |  |  | Account 2 | 2021-06-25 08:42:04.517000 |  |  |  |  |  | Incident | P3 | 2021-06-03 11:00:00 | True | 2021-05-31 22:48:00.183000
 | Account 1 | 1 | Sunday | 1 | Closed | Closed |  |  |  |  |  | Account 2 | 2021-06-28 10:23:50.630000 | 2021-06-28 10:30:16.257000 |  |  |  |  | Incident | P3 | 2021-06-07 13:44:00 | False | 2021-06-21 08:38:32.843000 | 1364
 | Account 2 | 2 | Monday | 0 | 06:00:00 | 20:00:00 |  |  |  |  |  |  |  |  |  |  |  |  | Incident | P3 | 2021-06-15 09:50:00 | False | 2021-05-31 22:57:50.260000 | 3770
 | Account 2 | 3 | Tuesday | 0 | 06:00:00 | 20:00:00 |  |  |  |  |  |  |  |  |  |  |  |  | Incident | P3 | 2021-06-16 10:29:00 | False | 2021-05-31 23:02:23.803000 | 4409
 | Account 2 | 4 | Wednesday | 0 | 06:00:00 | 20:00:00
 | Account 2 | 5 | Thursday | 0 | 06:00:00 | 20:00:00 |  |  | With holidays
 | Account 2 | 6 | Friday | 0 | 06:00:00 | 20:00:00 |  |  | without holidays
 | Account 2 | 7 | Saturday | 0 | 08:00:00 | 18:00:00 |  |  |  | =IF(AND($A4>0,$B4>0),IF(INT($A4)-INT($B4)=0,MAX(0,MIN($T$3,ROUND(MOD($B4,1),6))-MAX($S$3,ROUND(MOD($A4,1),6))),MAX(0,MIN($T$3,ROUND(MOD($B4,1),6))-$S$3)+($T$3-MAX(0,MAX($S$3,ROUND(MOD($A4,1),6))))+(INT($B4)-INT($A4)>1)*($T$3-$S$3)*NETWORKDAYS(INT($A4)+1,INT($B4)-1)),IF(INT($A4)-INT(NOW())=0,MAX(0,MIN($T$3,ROUND(MOD(NOW(),1),6))-MAX($S$3,ROUND(MOD($A4,1),6))),MAX(0,MIN($T$3,ROUND(MOD(NOW(),1),6))-$S$3)+($T$3-MAX(0,MAX($S$3,ROUND(MOD($A4,1),6))))+(INT(NOW())-INT($A4)>1)*($T$3-$S$3)*NETWORKDAYS(INT($A4)+1,INT(NOW())-1)))
 | Account 2 | 1 | Sunday | 0 | 08:00:00 | 18:00:00 |  |  |  | =IF(AND($A4>0,$B4>0),IF(INT($A4)-INT($B4)=0,MAX(0,MIN($T$3,ROUND(MOD($B4,1),6))-MAX($S$3,ROUND(MOD($A4,1),6))),MAX(0,MIN($T$3,ROUND(MOD($B4,1),6))-$S$3)+($T$3-MAX(0,MAX($S$3,ROUND(MOD($A4,1),6))))+(INT($B4)-INT($A4)>1)*($T$3-$S$3)*NETWORKDAYS(INT($A4)+1,INT($B4)-1)),IF(INT($A4)-INT(NOW())=0,MAX(0,MIN($T$3,ROUND(MOD(NOW(),1),6))-MAX($S$3,ROUND(MOD($A4,1),6))),MAX(0,MIN($T$3,ROUND(MOD(NOW(),1),6))-$S$3)+($T$3-MAX(0,MAX($S$3,ROUND(MOD($A4,1),6))))+(INT(NOW())-INT($A4)>1)*($T$3-$S$3)*NETWORKDAYS(INT($A4)+1,INT(NOW())-1)))