Forum Discussion
Counting of business and non business hours white a ticket was open
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 used time acoarding to a special time like from sun to Thu the business hours from( 8:00-16:00) and from(16:00 - 00:00) while from 00:00-8:00 should be count as non business hours.. But on fri and saturday the whole working hours are count as non business hours..
Thank you
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)
9 Replies
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.
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
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)
