Calculate only business hours irrespective of submit time in business hours

[Shekar M K]

I have a data in excel showing the submit date and resolved date of a ticket and i am looking for formula to calculate only business hours time spent on a ticket.


Example :
StartDate and time = The time the ticket came into the system
EndDate and time = The time the agent closed the ticket

Business hours are 08:00 - 20:00 Hrs

Weekends and after hours are not included in time spent and there are no national Holiday's included.

ex.
StartDT 11/3/2016 17:00
EndDT 13/03/2016 7:00

Scenario: A case comes in at 17:00 on Friday 11/3/2016. The case is closed at 07:00 on Monday 13/03/2016. Total time spent (calculated manually) is 3 hours.

Based on one of the online search - i got one formula but i am not able to apply to the calculation field

(NETWORKDAYS(StartDT,EndDT)-1)*(TIME(20,0,0)-TIME(8,0,0))+IF(NETWORKDAYS(EndDT,EndDT),MEDIAN(MOD(EndDT,1),TIME(20,0,0)-TIME(8,0,0)),StopTime)-MEDIAN(NETWORKDAYS(StartDT,StartDT)*MOD(StartDT,1)TIME(20,0,0)-TIME(8,0,0))

Please help !!

Regards,
Shekar M K

 

[Shekar M K]

Hi all,

Its high priority for me and any help would be appreciated !!

Regards
Shekar M K

 

[Chihiro]

I'm sure there's smarter way of doing it. But below formula should work.
=IF(NETWORKDAYS(INT(B2+1),INT(C2))=1,IF(MOD(B2,1)<20/24,(20/24-MEDIAN(MOD(B2,1),8/24,20/24)),0)+IF(MOD(C2,1)>8/24,MOD(C2,1)-8/24,0),IF(NETWORKDAYS(INT(B2+1),INT(C2))<0,MOD(C2,1)-MOD(B2,1),(NETWORKDAYS(INT(B2+1),INT(C2))-1)*12/24+IF(MOD(B2,1)<20/24,(20/24-MEDIAN(MOD(B2,1),8/24,20/24)),0)+IF(MOD(C2,1)>8/24,MOD(C2,1)-8/24,0)))*24

See attached.

 

[Chihiro]

Brain fart, looking at the formula you don't need
IF(NETWORKDAYS(INT(B2+1),INT(C2))=1,,) portion. Since last portion accounts for this scenario as well.
So formula can be shortened to.
=IF(NETWORKDAYS(INT(B3+1),INT(C3))<0,MOD(C3,1)-MOD(B3,1),(NETWORKDAYS(INT(B3+1),INT(C3))-1)*12/24+IF(MOD(B3,1)<20/24,(20/24-MEDIAN(MOD(B3,1),8/24,20/24)),0)+IF(MOD(C3,1)>8/24,MOD(C3,1)-8/24,0))*24