Forum Discussion

Najat_abu_hamad's avatar
Najat_abu_hamad
Copper Contributor
Oct 01, 2019
Solved

Counting of business and non business hours white a ticket was open

Kindly your help I need your help in developing a formula to count the business and non business hours for a group of tickets.. I have data about entering and releasing time.. And i want to split the...
excel
Formulas and Functions
Macros and VBA
  • SergeiBaklan's avatar
    SergeiBaklan
    Oct 02, 2019

    Najat_abu_hamad 

    If add helper columns like this

    not to harcode constants, the formula could be

    =24*(
   IF(
      INT(Q2)-INT(P2)>2,
      NETWORKDAYS.INTL(P2+1,Q2-1,7),0
   ) +
   IF(
      INT(Q2)=INT(P2),
      MIN(MOD(Q2,1),$AF$2)-MAX(MOD(P2,1),$AE$2),
      ($AF$2-MAX(MOD(P2,1),$AE$2))*(WEEKDAY(P2,15)>2) +
      MAX(MOD(Q2,1)-$AE$2,0)*(WEEKDAY(Q2,15)>2)
   )
)

    result in hours (e.g. 1.5 hour, not 01:30)

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Oct 02, 2019

Najat_abu_hamad 

If add helper columns like this

not to harcode constants, the formula could be

=24*(
   IF(
      INT(Q2)-INT(P2)>2,
      NETWORKDAYS.INTL(P2+1,Q2-1,7),0
   ) +
   IF(
      INT(Q2)=INT(P2),
      MIN(MOD(Q2,1),$AF$2)-MAX(MOD(P2,1),$AE$2),
      ($AF$2-MAX(MOD(P2,1),$AE$2))*(WEEKDAY(P2,15)>2) +
      MAX(MOD(Q2,1)-$AE$2,0)*(WEEKDAY(Q2,15)>2)
   )
)

result in hours (e.g. 1.5 hour, not 01:30)

Najat_abu_hamad's avatar
Najat_abu_hamad
Copper Contributor
Oct 02, 2019

HiSergeiBaklan 

Thanks a lot for your help,it is effective formula for the most of data but the problem now is when i tried to drag and drop the formula to the rest of data, in some cells it gives me #name! or #num! excel error as shown on the attached file, and on some cells like cell(Z3), the formula results ( in business hours> duration ), and that is not correct and therefore gives negative # of non business hours.. 

to solve the negativity problem i observed that this issue only happened when non business hours=0,so i can filter these cells and choose business hours = duration directly.

Thanks for your attention. I'm looking forward to your reply

 

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Oct 02, 2019

    Najat_abu_hamad 

    Something strange, I opened the file you attached to the latest post and see no one #NAME! or #NUM! error. Calculations are also correct (at least Business Hours are less than Duration)

    All negative results are -0.01 as at the bottom right of above screenshort. That's rounding error, you may entire formula for Non-Busiess hours as

    =IF(<formula> <0, 0, <formula>)

    I recalculate the sheet, please check attached again.