Formula for calculation time differences by business hours
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 :)
Answers
-
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
-
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?
-
Yes because we are measuring metrics for the time our Techs to go onsite to respond to a Service call
Thank you
-
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?
-
Yes Please
-
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?
-
Weekends would be excluded and holidays are
-
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?
-
Yes in excel that is how I do it
I think keeping that in a separate sheet in smartsheet would be practical
-
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?
-
4 hours and 4 minutes is correct as we do not count hours from 5:01pm to 7:59 am
Thank you
-
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?
-
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
-
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.
-
Yes that is correct :)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!