Formula for calculation time differences by business hours

07/17/20
Answered - Pending Review

@Paul Newcome

I am looking to calculated the time difference in business hours which are 8am to 5pm and also exclude holidays

Below is an example of the data and there are no breaks. this is time worked on a service call.


Thank you :)



Previous1

Answers

  • Debbie JDebbie J ✭✭✭✭✭

    Actually this one is an odd example because the onsite was during a non Business hour time

    So the ticket could be created 06/26/20 12:56 and the onsite time could be 06/27/20 08:30

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Am I correct in assuming that your "Created" columns would be the start date/time and the "First Visit" columns would be the end date/time?

    thinkspi.com

  • Debbie JDebbie J ✭✭✭✭✭

    Yes because we are measuring metrics for the time our Techs to go onsite to respond to a Service call


    Thank you

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    And with the time in the example being outside of your 0800-1700 range, would you want that to be considered 0800 the next day?

    thinkspi.com

  • Debbie JDebbie J ✭✭✭✭✭

    Yes Please

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    And you said to exclude holidays. What about weekends? Do you have a comprehensive list of dates you want excluded for holidays, or would using the dates built into the WORKDAY (and other similar) functions work for you?

    thinkspi.com

  • Debbie JDebbie J ✭✭✭✭✭

    Weekends would be excluded and holidays are


  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Ok. I will work on putting something together, but I can't promise by a specific deadline. Work is picking up for me lately, so I don't have quite as much free time.


    Would it be safe to assume that your holiday listing is on a separate sheet?

    thinkspi.com

  • Debbie JDebbie J ✭✭✭✭✭

    Yes in excel that is how I do it

    I think keeping that in a separate sheet in smartsheet would be practical

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    And just to make sure I am understanding correctly...


    In your screenshot above, the result would be 4 hours and 4 minutes or 19 hours and 34 minutes?

    thinkspi.com

  • Debbie JDebbie J ✭✭✭✭✭

    4 hours and 4 minutes is correct as we do not count hours from 5:01pm to 7:59 am


    Thank you

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    I also just realized... Are you wanting to exclude weekends? If so, in your above example the On Site date would actually be 6/29.

    You also listed in your first comment that the On Site Time would be 08:30, but in your original post you say your working hours start at 08:00.

    Could you please provide some clarity on these?

    thinkspi.com

  • Debbie JDebbie J ✭✭✭✭✭

    Hi


    I realize that example wasn't the best

    Please review the examples listed below

    The times the onsight would be M-F.Anything on the weekend would be a different metric and not part of this group



  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Ok. Could you please take a look at the below screenshot and confirm if the entries are accurate? I put in notes for those dates/times that got shifted before calculation.


    Note: The below screenshot results are manually entered. I will need to put together formulas and whatnot still but want to make sure I am working towards the correct results first.


    thinkspi.com

  • Debbie JDebbie J ✭✭✭✭✭

    Yes that is correct :)

Sign In or Register to comment.