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!

 

[barry houdini]

Hello sticky, welcome to MrExcel

Which formula are you using, the short one or the longer one?

From what you say I assume you have a time period like 08:00 - 17:00 but because times are PST that will have to be 7 hours earlier, i.e. 01:00 to 10:00.

What's the actual time period?

 

[mackky23]

Hi,

what if....

StartDT: 16/10/2014 15:54
EndDT: 16/10/2014 17:52
StartTime: 15:00
StopTime: 00:00


The expected result should be 1:58 ([h]:mm:ss)

 

[sa7toot]

Hello all,

I have tried the above different formulas but unfortunately without success. My requirement is similar, but I have small differences:

1- Working Hours are from 06:00 am until 11:00 pm everyday.
2- There are no weekends or holidays
3- Tickets can be opened during any time of the day.
4- A ticket can remain open for a number of days, example, it can be opened on 01-Oct-2015 10:00:00 and closed on 03-Oct-2015 13:00:00

I use the above criteria to calculate the SLA penalties for our outsourcing agent.

What is the best formula I can use?

 

[Analyst1982]

Dear Friends,

I have similar kind of question but not yet able to figure out.

I have the following:

Request Date Request Time Response Date Response Time

27/11/2015 12:30 Pm 30/11/2015 10:15 am

We have business days Sunday - Thursday (7:15 am until 2:30 PM)

I want to calculate the TAT based on the above criteria, so for the above example 27/11/2015 and 28 is holiday therefore not TAT calculation. TAT starts on sunday (29/11/2015). if I calculate manually, it will be 7:15 on Sunday and on Monday 3 more hours so total hours will be 10:15 to perform a task.

I would really appreciate if you can assist me on this.

 

[HemaV]

Hello Barry,

I have tried the above formula and it is not working as expected.

For example:

StartDate: 3/23/16 17:13:40
End Date: 3/23/2016 19:17:00
StartTime: 8:30:00 AM
StopTime: 5:00:00 PM

When use your formula, the timespent gives me: 1:08:30, i.e., 0 days 1 hrs and 8 mins

However, when I do a manual calculation the correct timespent should have been 17 mins as the clock stops at 17:00

Please help me out!!

Thank you

 

[RomzyVincy]

OK, Eric, in that case you can use this version

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

Hi Barry,

I tried the formula but I am getting a negative result. Please advise

 

[RomzyVincy]

Hi Barry,

Base on the thread this formula account 24 hours in a day but just count the duration of start time and end time and it excludes weekends. I am not sure if I have it correctly. Please advise and thanks in advance .

Romzy

 

[Golen88]

Hi guys!

I'm using this formula:
=(NETWORKDAYS(StartDT,EndDT)-1)*(StopTime-StartTime)+IF(NETWORKDAYS(EndDT,EndDT),MEDIAN(MOD(EndDT,1),StopTime,StartTime),StopTime)-MEDIAN(NETWORKDAYS(StartDT,StartDT)*MOD(StartDT,1),StopTime,StartTime)

And i need to add openinghours om Saturday. On saturday opening hours are 08:00 - 16:00. Monday to Friday it's 08:00 - 20:00. Sunday is closed.

Can someone help with this?

 

[jason1000rr]

Hello Everyone--
This formulate from barry works perfectly. My question is how do we modify it to exclude lunch breaks? Thank you!

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

 

[James006]

Hello,

You can take a look at following link: How to Calculate Net Work Hours Between Two Dates - Excel Tactics

HTH