Calculating total time between start date\time and End date\time excluding non business

I have seen quite a few links using the network day functionality, but have not been able to put them into my spreadsheet. This is for Service level managment. We need to figure out ticket age taking away non business hours and amount of time that a ticket was put in pending status. If there is no closed time then the status would be returned.

Any help would be appreciated.

Thank you in advance.

If you could fill in the answers on a couple of these so I can see what you want it to look like and also explain how you did your calculation then I could probably figure out the formula to do it automatically.

Thanks!

Sorry, it looks like my second xls did not have my changes in it.


Times are critical as these are time stamps for trouble tickets and we are trying to determine if the ticket breached a service level agreement

D1 is the time the ticket was submitted by a customer (can be anytime of the day, any day of the week, in or outside of business hours)

E1 is the time the ticket was resolved
F1 would be if the tech that the ticket was assigned to had to put the ticket into pending status (for instance, waiting for the customer to return his call)
G1 is the time that the customer returned the call and the tech started working the issue again.

So Total time should be (E1-D1)-(G1-F1) and not include any hours that are outside of 2am to 6pm (monday thru friday)

Closed date should always be later than either of the pending dates. Column H is what I beleive the answer should be.

Thanks again for your help. Hopefully this clears your questions up.

Okay I think I'm okay and I'm going to start working on this, but just tell me if I missed something here. For your first example I come up with 25 hours not 26. The ticket was started on 1/31/2015 a Saturday, so the clock doesn't start ticking until 0200 on Monday 2/2. So then from that time until the pending start is 7 hours. The pending end times comes the same day at 1500, so we have 3 hours left until end of day. So we have 10 hours for 2/2. Then on the next day we go from 0200 until ticket closed at 1700, 15 hours. So I get 25, is this correct? I agree on the other number so I'll assume yes. Is there a possibility of multiple pendings? I don't know what the business is, so maybe awaiting a call for customer to approve further repairs or a change in estimated price, something like that, might be two or more pendings?

Yes.. you are right. 25 not 26 hours.

no chance of multiple pendings. (a limitation of our ticketing tool)

Thanks again for all your help and time.

Jon

Hi Jon,

Sorry for the delay. Okay I'm not mister formula and if there is one that can work for you it's going to be tricky and I'm sure long. What I did was I wrote a custom function. I've tested it a little, but you should check it out and see how it works for you.
I would suggest you make a copy sheet to test on.
You workbook needs to be in a .xlsm format as this function is a macro and you need to enable macros for the workbook.
Copy the code below, choose Control + F11 in you workbook this will take you into the visual basic editor. Go to the insert menu and choose "Module", when you insert the module an empty white window will come up. Paste the code there and just close this window to go back to Excel. The function is very similar to any other function you use like Sum or Average etc.

The best way to use it is highlight a cell where the formula will be and click the "fx" button up above the column letters if you have them turned on to the left of the formula editor where you see the text you are typing into a cell. A box will pop up that says insert function at the top. There's a drop down box that says "Or select a category:" choose this drop down box and scroll down to User Defined. Click on the function that says "CalculateTicketHours", should be the only one there and choose ok. The box that pops up is one of the type where you click on the box and then choose the cell. As you can see on the left is a clue as to what cell needs to go in each box. Make sure you put in the pending cells also even if they are empty and or you know they won't be used. If you don't it will cause an error. If you don't have pending cells then type a zero into each one of those cells and it'll work fine. The reason I want you to go to this box is that when you input a built in Excel function like = Sum as you are typing it there's a little window that pops up that give you clues as to what to put where, but with a user defined function you don't get that, but if you open the insert function box I've given you the clues you need. Once you get used to it and you know what needs to go where and in what order you can just type =CalculateTicketHours( and put the cells in) hit enter and you are good to go.

Good Luck,
Bruce

Please Login or Register  to view this content.

Here it is plugged in and working.

I did find some errors. This version should work well. Follow the instructions in post #8, to install this in another spreadsheet if the attached is not the on you wish to use.
The attached spreadsheet has the code in place and working.

Please Login or Register  to view this content.