Counting difference between hours if outside of business hours

Hi All,

I'm working on an excel spreadsheet and I need to sum the hours between two different times (basically, counting how many hours a call was open - the days open is listed in another column which works fine).

My time is placed into its own column on purpose, I have a "Time Allocated" column and a "Passback" column and I wish to calculate how many hours were between the two times within work hours of 8:30AM and 5:00PM.

EDIT: I forgot I can't actually upload at work so it'll have to wait.. but it's a lot like this:

Call ID Depo Call Type Priority Time Allocated Allocated to us Installs Closed/Reallocated Reall Passback Instances DO HMO
110538 PTCSYD ETS Equipment P4 4:25 PM 14-Jun-2013 17-Jun-2013 2:21 PM 3 21:56

(Just plop it into Excel, tried to format it but it's a ***** on this site. result I'm looking for in the above is 6 hours 26 minutes or something like that)

The formula is for tracking KPI's but the counter stops after 5:00PM until 8:30AM the next morning.
I need to replicate this function in excel so I can assign a priority to them.

The HMO column is where the result should go, so if the call was raised at 3:00PM and we closed it at 9:00am, the HMO Column should only show 2:30.
It also should handle times where the time allocated is minus the passback (because it was raised the previous day or something).

The formula I was using to get the hours (without stopping the count of hours after 5PM to 8:30AM) was:

=IF(Table42[[#This Row],[Time Allocated]]>Table42[[#This Row],[Passback]],IF(Table42[[#This Row],[Time Allocated]]>Table42[[#This Row],[Passback]],Table42[[#This Row],[Passback]]+1-Table42[[#This Row],[Time Allocated]],Table42[[#This Row],[Time Allocated]]-Table42[[#This Row],[Passback]]),IF(Table42[[#This Row],[Passback]]-Table42[[#This Row],[Time Allocated]]=0,"",Table42[[#This Row],[Passback]]-Table42[[#This Row],[Time Allocated]]))


Sorry if this is a long post, not too sure how I'm going to describe it I'm pretty noobish at Excel.

Thanks in advance for any help guys,
Phil

Hi Joytech,

welcome to the forum.

Please upload a sample workbook to support your query. Thanks


Regards,
DILIPandey
<click on below * if this helps>

Hi Joytech22,

3:59 PM 20-Jun-2013 minus 25-Jun-2013 7:52 AM = 15:52
How you got this result for call Id 110778 ?




Regards,
DILIPandey
<click on below * if this helps>

Originally Posted by dilipandey

Hi Joytech22,

3:59 PM 20-Jun-2013 minus 25-Jun-2013 7:52 AM = 15:52
How you got this result for call Id 110778 ?




Regards,
DILIPandey
<click on below * if this helps>

I'll upload a spreadsheet with the formula I used:
Example.xlsx

Okay... so are you satisfied with that output ? or you are looking for some other result there, and if so then what is your expected result ?


Regards,
DILIPandey
<click on below * if this helps>

Not satisfied at all.

The result I'm looking for from 110778 in the HMO column is 1 hour 1 minute (or 1:01) - I explain below.

There are two/four columns in the spreadsheet worth noting, the DO and HMO columns both serve different functions.
DO calculates days open using the two date columns while the HMO determines the total hours difference between the "Time Allocate" and "Passback" columns.

DO works fine, I can't figure out a formula for HMO to calculate the difference between "Time Allocate" and "Passback" between the hours of 8:30AM and 5:00PM. I want to ignore or minus counting any time before and after 8:30AM and 5:00PM. At the moment it just counts the difference without consideration for the time rules I want to set, which is upsetting my KPI's.

For example.. Call ID 110778 column HMO should show exactly 1:01 because the counter stops at 5PM, the call was passed back before work hours (8:30AM).

As you can probably tell, I'm not too experienced with Excel.

Okay... can you provide your expected output for few more Call IDs like you provided 1:01 for 110778 ? Thanks.


Regards,
DILIPandey
<click on below * if this helps>

Okay.. see the attached file and let me know if any scenario need to be adjusted / added:-


Book1.xls

Regards,
DILIPandey
<click on below * if this helps>

sorry did actually read all the back log

seems to only occur when it is the same day
if that case

put into m17 check for less than 1 day

Please Login or Register  to view this content.

i note something about after hours which is 17,0,0
so

Please Login or Register  to view this content.

Originally Posted by kvsrinivasamurthy

Pl see the attached file.

Hi kvsrinivasamurthy,
Your formula doesn't quite work properly for a few scenarios.
In J7 the result is 8:00, but the call was opened and closed at 8:00 AM and therefore the result should be 0:00 because it was outside of working hours.

J8 has a result of 1:30, the proper result is actually 0:30 because we start counting at 8:30AM and I'm not sure where the extra hour comes from.

J9 shows 23:00 which would be impossible in this situation, because the maximum duration for any result is 8:30 (we purely want to calculate the time the call was open, ignoring all time and dates before 8:30am and after 5:00 PM therefore every result afterwards is counting every day as hours. Please see my previously uploaded example for an idea on what I require.

Thanks for the effort though, I'll probably save your formula for use in another project. Both yours and humdingaling's (whos result is more what I'm looking for minus a few strange results)