Formula for calculation time differences by business hours

Debbie J

@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 :)

Debbie 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 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?

Debbie J

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

Thank you

Paul 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?

Debbie J

Yes Please

Paul 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?

Debbie J

Weekends would be excluded and holidays are

Paul 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?

Debbie J

Yes in excel that is how I do it

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

Paul 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?

Debbie J

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

Thank you

Paul 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?

Debbie 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 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.

Debbie J

Yes that is correct :)