+ Reply to Thread
Results 1 to 10 of 10

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

  1. #1
    Registered User
    Join Date
    02-12-2015
    Location
    Tacoma, WA
    MS-Off Ver
    2007
    Posts
    4

    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.
    Attached Files Attached Files

  2. #2
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2,530

    Re: Calculating total time between start date\time and End date\time excluding non busines

    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!

  3. #3
    Registered User
    Join Date
    02-12-2015
    Location
    Tacoma, WA
    MS-Off Ver
    2007
    Posts
    4

    Re: Calculating total time between start date\time and End date\time excluding non busines

    Attached is what I believe the totals should be.. (new xls) Column H. Tickets can come in anytime.. during office hours or off hours. We don't want to count time that is outside of business hours.

    Support works 16 hours a day.. (2am to 6pm) Monday thru Friday.

    H2 =
    1/31 0 hours
    2/1 0 hours
    2/2 11 hours (16 total business hours minus 5 hours in pending status)
    2/3 15 hours

    h3=
    1/31 0 hours
    2/1 0 hours
    2/2 16 hours
    2/3 15 hours
    Attached Files Attached Files
    Last edited by jonashlock; 02-12-2015 at 05:39 PM.

  4. #4
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2,530

    Re: Calculating total time between start date\time and End date\time excluding non busines

    Okay so in D1 we have a created date time, you haven't explained the significance of that and whether I should use that somehow.
    In E1 you have a closed date time, same question.
    In F1 you have pending start date time, I don't know how that fits in.
    In G1 same questions..
    D1 = Jan. 12
    E1 = Feb. 1
    F1 = Jan. 16
    G1= Jan. 29

    Then you have this:
    1/31 0 hours
    2/1 0 hours
    2/2 11 hours (16 total business hours minus 5 hours in pending status)
    2/3 15 hours

    2/2, 2/3 are beyond the other dates and one date E1 is closed date and G1 is pending end date time. I don't understand.

    See my confusion? I can't come up with a formula without an understand as to how you would calculate it by hand.
    Last edited by skywriter; 02-13-2015 at 12:10 PM.

  5. #5
    Registered User
    Join Date
    02-12-2015
    Location
    Tacoma, WA
    MS-Off Ver
    2007
    Posts
    4

    Re: Calculating total time between start date\time and End date\time excluding non busines

    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.
    Attached Files Attached Files

  6. #6
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2,530

    Re: Calculating total time between start date\time and End date\time excluding non busines

    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?

  7. #7
    Registered User
    Join Date
    02-12-2015
    Location
    Tacoma, WA
    MS-Off Ver
    2007
    Posts
    4

    Re: Calculating total time between start date\time and End date\time excluding non busines

    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

  8. #8
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2,530

    Re: Calculating total time between start date\time and End date\time excluding non busines

    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.

  9. #9
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2,530

    Cool Re: Calculating total time between start date\time and End date\time excluding non busines

    Here it is plugged in and working.
    Attached Files Attached Files

  10. #10
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2,530

    Re: Calculating total time between start date\time and End date\time excluding non busines

    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.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 3
    Last Post: 12-19-2013, 06:49 AM
  2. Need help Calculating Elasped time excluding non business hours.
    By hellpme in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-29-2013, 03:52 PM
  3. Calculation of time difference between the Start date & time & End Date & time
    By Harry Jones in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 12-17-2012, 03:54 PM
  4. Replies: 5
    Last Post: 03-21-2012, 08:07 AM
  5. [SOLVED] Calculating days & time left from start date/time to end date/time
    By marie in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 12-07-2005, 10:40 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1