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)

Vimal_Gaur's avatar
Vimal_Gaur
Brass Contributor
Oct 02, 2019

Hi Najat_abu_hamad,

 

You want to count business and non-business hours.

I have developed a formula according to the conditions you require, please check in the attached sheet and let me know to have detailed description, if it solves your problem.  

  • Najat_abu_hamad's avatar
    Najat_abu_hamad
    Copper Contributor
    Oct 02, 2019

    HiVimal_Gaur 

    Thank you for your effort...

    the point that most of the tickets were open between two dates,i.e:severaldays and different time schedule,

    here is the required task showing below and the attached file have the data you may need to understand the process:

    calculate the number of business and non-business hours accoarding to the table on the attached screenshoot (plz,make it configurable, not hardcoded as the time scheduling table may change, whereby the sum of business and non business hours should equal the difference between columns  Q-P 

     

     

    • 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