Calculate Time Difference (Business Hours Only)

[ianamck]

Hi Everyone

Rather than hijack a post from earlier today

http://chandoo.org/forum/threads/ca...ween-two-dates-times-by-business-hours.20261/

I though I should post a new question. I think the answer is a little simpler but working through the solution for the above post I think I am losing the plot.

I have four columns

Created
Changed
Closed Time Queue

02/08/2014 17:30 04/08/2014 12:30 05/08/2014 13:30 Queue 1
02/08/2014 17:30 02/08/2014 12:30 05/08/2014 13:30 Queue 2
02/08/2014 17:30 05/08/2014 12:30 07/08/2014 13:30 Queue 3


What I am trying to do is calculate the difference in hours from the Changed column and Closed Time Column. However this is where I am getting lost. The time difference must only be calculated in working hours. That is Monday to Friday and ONLY between the hours of 8:00am and 17:30am.

There is a second complication which is there are varying service level agreements (SLA) according which support queue the record is allocated.

So for example if it is in Queue 1 that would be a 2 hour SLA and Queue 2 is 4 hour SLA. If the difference is within SLA it should report completed in time. If it is outside SLA then it should report it failed and by how many hours / minutes.

Then to add even further complication. If it falls into for example Queue 3. Then the time difference does not rely on the working hours just working days. So for example the Queue 3 above would calculate as 49.5 hours and the SLA of 24 hours would be missed by 25.5 hours. \if it was within 24 hours then it would pass.

Any help or direction would be greatly appreciated.

Regards
Ian M

 

[Luke M]

Using this formula as basis:
http://chandoo.org/forum/threads/working-days-and-hours-only.12185/#post-71438

We can first calculate the hours with this formula:
=SUMPRODUCT((WEEKDAY(ROW(INDEX(A:A,A2):INDEX(A:A,C2)),2)<6)*9.5)
-(WEEKDAY(C2,2)<6)*MAX(0,MIN(9.5,17.5-MOD(C2,1)*24))
-(WEEKDAY(A2,2)<6)*MAX(0,MIN(7,MOD(A2,1)*24-9.5))

Then, we could use a VLOOKUP or something to see what Queue rules we should follow, and compare. However, at this point, I got stuck, as my times weren't matching up to what you stated in the above. See attached.

 

[ianamck]

Hi Luke

I always have problems with nested formulas. Thanks for your response. I took a look and love the solution but testing has shown some issues. I am wondering if I should treat the to types of SLA differently.

We have the calculation for working hours in a working day or the continuous hours working days only (no weekends).

Testing your solution I am getting a wrong answers though.

Row 1 work time should equal 10 hours 30 minutes
Row 2 works fine
Row 3 work time should be 49 hours not the 49.5 I had originally wrote

Do you think I need some more fingers and toes?

 

[Luke M]

Doh, it helps if I can follow my own instructions. I had switched two of the numbers. Corrected file attached.

 

[ianamck]

Hi Luke sorry for the late reply, excessively long day and all that.

Seems to work brilliantly for the first two example lines, Many thanks.

The only issue I have now is the third line example and that why I was asking if maybe we should treat that type of SLA completely differently. It now shows the correct amount Work Time (49) Many Thanks.

But with this example the calculation comes out as 25 hours over in my brain??? And if you set the closed time to equal 48 hours worked then it shows over by 0:00.

With all of the nested functions I am struggling to work out which bit I need to tweak. Will try and see if I can work something out but if you have anny ideas it would be much appreciated as always.

Ian M

 

[NARAYANK991]

Hi Ian ,

Can you explain how you have arrived at 49 hours or 49.5 hours ( not really so material ) given that you want to calculate :

the difference in hours from the Changed column and Closed Time Column.

and the Changed value is : 05/08/2014 12:30
and the Closed value is : 07/08/2014 13:30

That is my ongoing complaint about excessively complicated single-cell formulae ; verifying them is difficult , and you never know whether your data will prove them wrong , because it is only data which when it leads to wrong outputs will show up the formulae as wrong.

Using helper columns when there is no constraint is , according to me , the correct way , since when there are mistakes in the formulae , they can be verified and pointed out by anyone.

I am attaching a file which has some time calculations , which you can verify ; if they are wrong , please say so , and I am sure they can be corrected easily and immediately , again , by anyone.

Narayan

 

[ianamck]

Hi Narayan

thanks for helping out. I understand the helper column suggestion but looking at your example I have got completely lost again. Luke's last solution is working for me apart from the third row.

Just to review where we started.

1. Created Date is when we get a support ticket arrive in a queue
   
   
2. Changed Date is when someone opens the ticket and also when the clock starts. However the worked time on a ticket can only be calculated when closed. The time calculation should only count working hours (Monday to Friday)

Once you have the total hours worked on a ticket and assigned a queue type you can then lookup if it met that particular queues SLA so it will either pass or report number of hours it failed by.

The third row example from Luke now reports the correct hours worked 49 hours as it should work out start and close point during the working week and ignore opening hours (unlike row 1 and 2). EG ticket is changed at 10:00am on working day one and is closed at 11:00 am two working days later it would be 49 hours worked. The bit that appears not to work in row 3 is that is only reporting 1 hour over the SLA not the 25 it should report. This is why I suggested earlier on that as the calculation for a queue 3 type ticket might need to be approached differently, but was hoping for a one stop formula which Luke was very close to.

I hope this makes sense now Narayan if not please ask.

 

[NARAYANK991]

Hi Ian ,

Sorry , but my question is still unanswered , I think.

You have mentioned that you wish to calculate the time difference between the data labelled Changed , and the data labelled Closed ; is this correct ?

Secondly , you wish to calculate the time difference based on a specified number of working hours each day ( between the specified Start Time and End Time ) and excluding weekends and holidays. Is this correct ?

Lastly , given the above , and given that the values of Changed and Closed are :

05/08/2014 12:30
and 07/08/2014 13:30

how do you arrive at 49 ?

Narayan

 

[ianamck]

Narayan

You have all the correct information.

The confusion comes from the fact that when it falls under a 24 SLA the clock starts at 05/08/2014 12:30 and finishes 07/08/2014 13:30 and calculates the complete time worked. a 24 hour SLA ignores the the working hours of the days but would exclude weekends (holidays are not a requirement).

This is why I ws thinking it would need to be treated differently n the 24 hour SLA. Luke managed to get the correct calculation working as in 49 hours worked. The bit that didn't seem to work is it reported it as only missing the SLA by and hour not 25 hours.

Does that make it a bit clearer???

Might be useful to know I am working in Excel 2007.

Many thanks Ian M

 

[NARAYANK991]

Hi Ian ,

Thanks for the clarifications ; everything is clear.

All that is needed is lots of data , where you have manually worked out the outputs , so that we can compare the results of the formula with what you have got by working it out manually.

Narayan

 

[ianamck]

Hi Narayan

Created some sample data and calculated the results manually. Hopefully it all makes sense.

Regards
Ian M

 

[Mike H..]

Hi Narayan

Created some sample data and calculated the results manually. Hopefully it all makes sense.

Regards
Ian M

Hi,

I've re-read this a dozen times and cannot get my head around the rules for Queue3. The data below is for the first Q3 entry (Row 10) and for that data you expect an answer of 72 hours.

The times in the data below are not the start and end times they are the hours in each day which need to be counted or ignored. Please explain which hours are ignored and if necessary explain the rule for ignoring them.

The formula for Q1 & Q2 seems easy enough and is this.

=((NETWORKDAYS(B2,C2)-1)*("17:30"-"08:00")+MEDIAN(MOD(C2,1),"08:00","17:30")-MEDIAN(MOD(B2,1),"08:00","17:30"))*24

Thursday 14/8 05:25
Friday 15/8 24:00
Saturday 16/8 24:00
Sunday 17/8 24:00
Monday 18/8 24:00
Tuesday 19/8 04:05

 

[ianamck]

Hi Mike you think you have a headache imagine mine.

Queue 3 rules are the clock starts running from the second a ticket is changed and ends the second the ticket is closed. It then counts hours passed but excludes weekend hours. So for example ticket changes at 11:00am on a Thursday and close on Friday at 11:00am 24 hours have passed.

However if it closed the following Monday at 11:00am although 96 hours have passed it should ignore weekend hours.

Hope that answers your question. As stated before I was looking for a one shot formula and this seems to be a major issue. Maybe I should just use the same rules for all 3 queue types and then manipulate the calculation if the result is in queue 3.

 

[Mike H..]

Hi Mike you think you have a headache imagine mine.

Queue 3 rules are the clock starts running from the second a ticket is changed and ends the second the ticket is closed. It then counts hours passed but excludes weekend hours. So for example ticket changes at 11:00am on a Thursday and close on Friday at 11:00am 24 hours have passed.

However if it closed the following Monday at 11:00am although 96 hours have passed it should ignore weekend hours.

Hope that answers your question. As stated before I was looking for a one shot formula and this seems to be a major issue. Maybe I should just use the same rules for all 3 queue types and then manipulate the calculation if the result is in queue 3.

Hi,

OK, I'm past my elderly moment, I got it. The formula for row 2 is this. Drag down. See you attached workbook.


=IF(D2="Queue 3",((C2-B2)*24)-SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(INT(B2)&":"&INT(C2))),2)>5))*24,((NETWORKDAYS(B2,C2)-1)*("17:30"-"08:00")+MEDIAN(MOD(C2,1),"08:00","17:30")-MEDIAN(MOD(B2,1),"08:00","17:30"))*24)

 

[ianamck]

Mike

Wow that looks like we have it. Let me play and test it.

Also I will have some real-time data to also test it against late tomorrow afternoon that will let me truly test the results.

Thank you for your effort and will come back to you as soon as.

Regards
Ian M

 

[Mike H..]

Mike

Wow that looks like we have it. Let me play and test it.

Also I will have some real-time data to also test it against late tomorrow afternoon that will let me truly test the results.

Thank you for your effort and will come back to you as soon as.

Regards
Ian M

Mike

Wow that looks like we have it. Let me play and test it.

Also I will have some real-time data to also test it against late tomorrow afternoon that will let me truly test the results.

Thank you for your effort and will come back to you as soon as.

Regards
Ian M

Hi,

I made some changes to the formula to include rounding and also added a formula to column F.

 

[Luke M]

Kudos to my colleagues for picking this up over the weekend. Looks like they've reached the solution.

 

[ianamck]

Hi Guys

Have not been well the past couple of days. Tooth extraction has caused an infection. Just started testing and will get back to you as soon as possible.

Regards
Ian M

 

[ianamck]

Ok

First off I have had to simplify the Q3 calculation for now as there is debate now going on as to the accurate SLA. This has caused all sorts of discussions. So for now we should just calculate all SLAs against possible working hours.

So I have some real data in the attached file but something appears to be broke. I am getting passes where there should be over by XX hours and mins.

 

[Mike H..]

Ok

First off I have had to simplify the Q3 calculation for now as there is debate now going on as to the accurate SLA. This has caused all sorts of discussions. So for now we should just calculate all SLAs against possible working hours.

So I have some real data in the attached file but something appears to be broke. I am getting passes where there should be over by XX hours and mins.

Hi,

Have a look now. You have to make a decision here. My original column was decimal hours (and still is) your PASS column was HH:MM so you were getting incorrect comparisons by comparing time with a decimal. I converted your pass column to decimal too.

EDIT...I added the conditional formatting to see what was going on, it's not necessary. I also uploaded a second workbook. In the second workbook on sheet 2 the data comparisons are all done in HH:MM

 

[ianamck]

Luke M / Narayan and Mike H

As always you have gone above and beyond with your help and support thank you and each of you. I continue to be astonished at your skills and hope one day I will be half as good. The important thing is keep learning which for me is the best part of the forum.

Tested and working fine once again thank you one and all.

Regards
Ian M

 

[ianamck]

Well Guys and Girls seems I have run into some problems and need some help please.

Have attached a sample file which shows some results that are correct but some don't seem to be working. The rows highlighted in yellow are reporting incorrectly. The rows highlighted in red are giving the incorrect result.

Any help appreciated

Regards
Ian M

 

[ianamck]

Is no one able to give an opinion???

No help out there??

Ian M

 

[Luke M]

Morning Ian.
Long holiday weekend here in the USA, perhaps that is cause for delay. I'll wait for @Mike H.. to respond, as he seemed to be on a good path.

 

[Mike H..]

Is no one able to give an opinion???

No help out there??

Ian M

Hi,

I need a while to remind myself of what we did here plus we have a family bereavement but I will respond if nobody else does in the meantime.