Time spent calculation: exclude non-business hours and weekends

[ebrandonwhite]

Thanks in advance for any help that can be offered.

I am using Excel 2007.

Variables:
StartDT = The time the ticket came into the system
EndDT = The time the agent closed the ticket
StartTime = The time the business day starts
StopTime = The time the business day ends
TimeSpent = The calculated time spent

Business hours are 0600 - 1800.

Weekends and after hours are not included in time spent.

ex.
StartDT
4/9/2010 17:00

EndDT
4/12/2010 7:00

Scenario: A case comes in at 17:00 on Friday 4/9/2010. The case is closed at 07:00 on Monday 4/12/2010. Total time spent (calculated manually) is 2 hours.

So the columns would look like:

|StartDT|EndDT|StartTime|StopTime|TimeSpent|


So what I need is a formula for the TimeSpent column that calculates the time spent using the given information in the previous columns.

Thanks!

 

[jit2d]

I need to make a similar calculation to calculate the total time where BH is between 9:00 AM to 5:00 PM Monday to Friday but exclude the pause time from the list. Also the holiday list & weekend to be excluded.


[TABLE="width: 1126"]
<colgroup><col><col span="3"><col><col span="3"></colgroup><tbody>[TR]
[TD]Initial Status Date[/TD]
[TD]Time In Initial Status (sec)[/TD]
[TD]Initial Status[/TD]
[TD]Final Status[/TD]
[TD]Transition Date[/TD]
[TD]Transition User[/TD]
[TD]Time In Final Status(sec)[/TD]
[TD]Remarks[/TD]
[/TR]
[TR]
[TD="align: right"]04-10-2016 10:58:43 PM[/TD]
[TD="align: right"]839[/TD]
[TD]Open[/TD]
[TD]Work In Progress[/TD]
[TD="align: right"]04-10-2016 11:12:42 PM[/TD]
[TD="align: right"]00:13:59[/TD]
[TD="align: right"]87997[/TD]
[TD]Start time[/TD]
[/TR]
[TR]
[TD="align: right"]04-10-2016 11:12:42 PM[/TD]
[TD="align: right"]87997[/TD]
[TD]Work In Progress[/TD]
[TD]Resolution Set[/TD]
[TD="align: right"]05-10-2016 11:39:19 PM[/TD]
[TD="align: right"]00:26:37[/TD]
[TD="align: right"]11[/TD]
[TD]Continue time[/TD]
[/TR]
[TR]
[TD="align: right"]05-10-2016 11:39:19 PM[/TD]
[TD="align: right"]11[/TD]
[TD]Resolution Set[/TD]
[TD]Solution Delivered[/TD]
[TD="align: right"]05-10-2016 11:39:30 PM[/TD]
[TD="align: right"]00:00:11[/TD]
[TD="align: right"]62545[/TD]
[TD]Continue time[/TD]
[/TR]
[TR]
[TD="align: right"]05-10-2016 11:39:30 PM[/TD]
[TD="align: right"]62545[/TD]
[TD]Solution Delivered[/TD]
[TD]Solution Verified[/TD]
[TD="align: right"]06-10-2016 05:01:55 PM[/TD]
[TD="align: right"]17:22:25[/TD]
[TD="align: right"]1[/TD]
[TD]Pause time[/TD]
[/TR]
[TR]
[TD="align: right"]06-10-2016 05:01:55 PM[/TD]
[TD="align: right"]1[/TD]
[TD]Solution Verified[/TD]
[TD]Closed[/TD]
[TD="align: right"]06-10-2016 05:01:56 PM[/TD]
[TD="align: right"]00:00:01[/TD]
[TD="align: right"]20332[/TD]
[TD]End Time[/TD]
[/TR]
</tbody>[/TABLE]

 

[Kankiriho]

Assuming that StartDT and EndDT are always within business hours you can use this formula

=(NETWORKDAYS(StartDT,EndDT)-1)*(StopTime-StartTime)+MOD(EndDT,1)-MOD(StartDT,1)

format result cell as [h]:mm

Hey Barry,

The situation here is Work comes in from Monday to Friday, 08:00am to 05:00pm. Work MUST come in during working hours but can be completed outside working hours and days. I need the formula that counts that when work is resolved outside working hours, it takes resolution time as in the previous working day at 05:00PM

Thanks

 

[xman323]

Guys, I need your help in calculating outage hours based on the below criteria:

Start Date & Time (A2)
End Date & Time (B2)
Daily Working Hours from 10:00 to 23:00
Friday working Hours are from 13:30 to 23:00
No days off and no holidays whatsoever