Formula for calculation time differences by business hours
Answers
-
Excellent. I'll get to work on automating it.
-
Take a look at THIS SHEET and let me know if you are able to get it working for you.
-
Looks good
Per the attached sheet
I do see one item for W867587 The 1/25/20 (Sat) was changed to 1/27 which is a Monday. The create time should be changed to 8:00 am, but it is showing as 11.13
Besides that it looks great
-
Take another look. I modified the formula in that column, so it should be pulling correctly now.
-
Oh. And the [CalcDiff] column is what you would use to pull metrics such as average times or total times.
-
Hi Paul everything looks great except for the Calcdiff column
Your Calc is not adding the days and maybe the samples I gave you did not have those examples
See the below formula and advise if I got this right. A 200% thank you on helping me as I would not have formatted this by myself. Especially knowing what the Excel formula looks like.
Calc Diff Column suggestion
=IF(SUM(StartDate@row – EndDate@row) > 0.99, ((StartDate@row - EndDate@row) * 9 + (EndTime@row - StartTime@row)),(EndTime@row - StartTime@row))
-
The days are calculated in the [End Time] column.
If the [Time Difference] column is correct, then so is the CalcDiff column as the [Time Difference] column pulls directly from that one.
-
I see now...
Thank you again !! so far looks great
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 455 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!