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.

Initial Status Date	Time In Initial Status (sec)	Initial Status	Final Status	Transition Date	Transition User	Time In Final Status(sec)	Remarks
04-10-2016 10:58:43 PM	839	Open	Work In Progress	04-10-2016 11:12:42 PM	00:13:59	87997	Start time
04-10-2016 11:12:42 PM	87997	Work In Progress	Resolution Set	05-10-2016 11:39:19 PM	00:26:37	11	Continue time
05-10-2016 11:39:19 PM	11	Resolution Set	Solution Delivered	05-10-2016 11:39:30 PM	00:00:11	62545	Continue time
05-10-2016 11:39:30 PM	62545	Solution Delivered	Solution Verified	06-10-2016 05:01:55 PM	17:22:25	1	Pause time
06-10-2016 05:01:55 PM	1	Solution Verified	Closed	06-10-2016 05:01:56 PM	00:00:01	20332	End Time

<colgroup><col><col span="3"><col><col span="3"></colgroup><tbody>
</tbody>

 

[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