SOLVED
Home

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

%3CLINGO-SUB%20id%3D%22lingo-sub-887983%22%20slang%3D%22en-US%22%3ECounting%20of%20business%20and%20non%20business%20hours%20white%20a%20ticket%20was%20open%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-887983%22%20slang%3D%22en-US%22%3EKindly%20your%20help%3CBR%20%2F%3EI%20need%20your%20help%20in%20developing%20a%20formula%20to%20count%20the%20business%20and%20non%20business%20hours%20for%20a%20group%20of%20tickets..%20I%20have%20data%20about%20entering%20and%20releasing%20time..%20And%20i%20want%20to%20split%20the%20used%20time%20acoarding%20to%20a%20special%20time%20like%20from%20sun%20to%20Thu%20the%20business%20hours%20from(%208%3A00-16%3A00)%20and%20from(16%3A00%20-%2000%3A00)%20while%20from%2000%3A00-8%3A00%20should%20be%20count%20as%20non%20business%20hours..%20But%20on%20fri%20and%20saturday%20the%20whole%20working%20hours%20are%20count%20as%20non%20business%20hours..%3CBR%20%2F%3EThank%20you%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-887983%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-888477%22%20slang%3D%22en-US%22%3ERe%3A%20Counting%20of%20business%20and%20non%20business%20hours%20white%20a%20ticket%20was%20open%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-888477%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F418485%22%20target%3D%22_blank%22%3E%40Najat_abu_hamad%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20want%20to%20count%20business%20and%20non-business%20hours.%3C%2FP%3E%3CP%3EI%20have%20developed%20a%20formula%20according%20to%20the%20conditions%20you%20require%2C%20please%20check%20in%20the%20attached%20sheet%20and%20let%20me%20know%20to%20have%20detailed%20description%2C%20if%20it%20solves%20your%20problem.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-888716%22%20slang%3D%22en-US%22%3ERe%3A%20Counting%20of%20business%20and%20non%20business%20hours%20white%20a%20ticket%20was%20open%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-888716%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F416201%22%20target%3D%22_blank%22%3E%40Vimal_Gaur%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20effort...%3C%2FP%3E%3CP%3Ethe%20point%20that%20most%20of%20the%20tickets%20were%20open%20between%20two%20dates%2Ci.e%3Aseveraldays%20and%20different%20time%20schedule%2C%3C%2FP%3E%3CP%3Ehere%20is%20the%20required%20task%20showing%20below%20and%20the%20attached%20file%20have%20the%20data%20you%20may%20need%20to%20understand%20the%20process%3A%3C%2FP%3E%3CP%3Ecalculate%20the%20number%20of%20business%20and%20non-business%20hours%20accoarding%20to%20the%20table%20on%20the%20attached%26nbsp%3Bscreenshoot%20(plz%2Cmake%26nbsp%3Bit%20configurable%2C%20not%20hardcoded%20as%20the%20time%20scheduling%20table%20may%20change%2C%20whereby%20the%20sum%20of%20business%20and%20non%20business%20hours%20should%20equal%20the%20difference%20between%20columns%26nbsp%3B%20Q-P%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-889325%22%20slang%3D%22en-US%22%3ERe%3A%20Counting%20of%20business%20and%20non%20business%20hours%20white%20a%20ticket%20was%20open%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-889325%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F418485%22%20target%3D%22_blank%22%3E%40Najat_abu_hamad%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20add%20helper%20columns%20like%20this%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20158px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F135133i4F5A4AF8F74B2FDD%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Enot%20to%20harcode%20constants%2C%20the%20formula%20could%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3D24*(%0A%20%20%20IF(%0A%20%20%20%20%20%20INT(Q2)-INT(P2)%26gt%3B2%2C%0A%20%20%20%20%20%20NETWORKDAYS.INTL(P2%2B1%2CQ2-1%2C7)%2C0%0A%20%20%20)%20%2B%0A%20%20%20IF(%0A%20%20%20%20%20%20INT(Q2)%3DINT(P2)%2C%0A%20%20%20%20%20%20MIN(MOD(Q2%2C1)%2C%24AF%242)-MAX(MOD(P2%2C1)%2C%24AE%242)%2C%0A%20%20%20%20%20%20(%24AF%242-MAX(MOD(P2%2C1)%2C%24AE%242))*(WEEKDAY(P2%2C15)%26gt%3B2)%20%2B%0A%20%20%20%20%20%20MAX(MOD(Q2%2C1)-%24AE%242%2C0)*(WEEKDAY(Q2%2C15)%26gt%3B2)%0A%20%20%20)%0A)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eresult%20in%20hours%20(e.g.%201.5%20hour%2C%20not%2001%3A30)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-889621%22%20slang%3D%22en-US%22%3ERe%3A%20Counting%20of%20business%20and%20non%20business%20hours%20white%20a%20ticket%20was%20open%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-889621%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20a%20lot%20for%20your%20help%2Cit%20is%20effective%20formula%20for%20the%20most%20of%20data%20but%20the%20problem%20now%20is%20when%20i%20tried%20to%20drag%20and%20drop%20the%20formula%20to%20the%20rest%20of%20data%2C%20in%20some%20cells%20it%20gives%20me%20%23name!%20or%20%23num!%20excel%20error%20as%20shown%20on%20the%20attached%20file%2C%20and%20on%20some%20cells%20like%20cell(Z3)%2C%20the%20formula%20results%20(%20in%20business%20hours%26gt%3B%20duration%20)%2C%20and%20that%20is%20not%20correct%20and%20therefore%20gives%20negative%20%23%20of%20non%20business%20hours..%26nbsp%3B%3C%2FP%3E%3CP%3Eto%20solve%20the%20negativity%20problem%20i%20observed%20that%20this%20issue%20only%20happened%20when%20non%20business%20hours%3D0%2Cso%20i%20can%20filter%20these%20cells%20and%20choose%20business%20hours%20%3D%20duration%20directly.%3C%2FP%3E%3CP%3EThanks%20for%20your%20attention.%20I'm%20looking%20forward%20to%20your%20reply%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-889779%22%20slang%3D%22en-US%22%3ERe%3A%20Counting%20of%20business%20and%20non%20business%20hours%20white%20a%20ticket%20was%20open%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-889779%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F418485%22%20target%3D%22_blank%22%3E%40Najat_abu_hamad%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESomething%20strange%2C%20I%20opened%20the%20file%20you%20attached%20to%20the%20latest%20post%20and%20see%20no%20one%20%23NAME!%20or%20%23NUM!%20error.%20Calculations%20are%20also%20correct%20(at%20least%20Business%20Hours%20are%20less%20than%20Duration)%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20573px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F135187i78FC49E94105B500%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EAll%20negative%20results%20are%20-0.01%20as%20at%20the%20bottom%20right%20of%20above%20screenshort.%20That's%20rounding%20error%2C%20you%20may%20entire%20formula%20for%20Non-Busiess%20hours%20as%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DIF(%3CFORMULA%3E%20%26lt%3B0%2C%200%2C%20%3CFORMULA%3E)%3C%2FFORMULA%3E%3C%2FFORMULA%3E%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EI%20recalculate%20the%20sheet%2C%20please%20check%20attached%20again.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-891020%22%20slang%3D%22en-US%22%3ERe%3A%20Counting%20of%20business%20and%20non%20business%20hours%20white%20a%20ticket%20was%20open%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-891020%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%3C%2FP%3E%3CP%3EThanks%20a%20lot%20for%20your%20help%2C%20the%20formula%20is%20working%20now%20with%20no%20errors.%3C%2FP%3E%3CP%3EAlso%2C%20I%20used%20the%20if%20condition%20to%20get%20rid%20of%20negative%20data%20on%20non%20business%20hours%2C%20and%20it%20works%20perfect%2C%20but%20the%20problem%20that%20some%20cells%20of%20(%23%26nbsp%3Bbusiness%20hours)%20is%20having%20negative%20numbers%2C%20when%20I%20filter%20I%20observed%20that%20this%20problem%20only%20appears%20when%20the%20duration%20in%20hours%20for%20this%20ticket(case)%20is%20very%20small%20and%20due%20to%20rounding%20problems.%3C%2FP%3E%3CP%3Eso%2C%20is%20there%20any%20way%20to%20solve%20this%20tiny%20problem%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-891207%22%20slang%3D%22en-US%22%3ERe%3A%20Counting%20of%20business%20and%20non%20business%20hours%20white%20a%20ticket%20was%20open%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-891207%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20formula%20works%20perfect.%3C%2FP%3E%3CP%3EThanks%20a%20lot%20for%20your%20time%20and%20effort.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-892214%22%20slang%3D%22en-US%22%3ERe%3A%20Counting%20of%20business%20and%20non%20business%20hours%20white%20a%20ticket%20was%20open%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-892214%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F418485%22%20target%3D%22_blank%22%3E%40Najat_abu_hamad%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E
Najat_abu_hamad
New Contributor
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 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
7 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.  

Hi@Vimal_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 

 

 

Solution

@Najat_abu_hamad 

If add helper columns like this

image.png

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)

Hi@Sergei Baklan 

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

 

@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)

image.png

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.

@Sergei Baklan 

The formula works perfect.

Thanks a lot for your time and effort.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
16 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
217 Replies