Handling request times within certain business hours
Hi
I am looking for a way to calculate the number of days between when a row is added and when it is marked as "complete."
I currently have a column set up with the submission timestamp. I also have a "date completed" column. I want to exclude weekends and holidays which seems simple enough, but I would also like to set a criteria that if a row is added past a particular time (6pm), that day is not counted towards the turnaround time.
I was thinking of trying to figure out a formula that could maybe be added to another column where if the time on the original submission was past 6pm, it would return the next date, and then do the networkdays formula between that date and the completed date. I haven't been able to come up with such a formula that would do this however. I am also open to completely different solution if there are better ways to do this.
Thanks a lot.
Comments

This will give you the hour that it was created:
=VALUE(MID(Created@row, FIND(" ", Created@row) + 1, FIND(":", Created@row)  FIND(" ", Created@row)  1))
You can use that in an IF/AND statement with this
=FIND("P", Created@row)
to flag if it is submitted after 6PM for the given date. You can either use it to check a box or write it directly into the formula you are using to calculate the number of days.
To check a box:
=IF(AND(VALUE(MID(Created@row, FIND(" ", Created@row) + 1, FIND(":", Created@row)  FIND(" ", Created@row)  1)) > 6, FIND("P", Created@row) > 0), 1)
.
You could then us an IF statement to calculate the days based on the box being checked or not.
=IF([Checkbox Column]@row = 1, Your Original Formula  1, Your Original Formula)
.
There are a few ways to write it if you are skipping the checkbox. The two most straight forward would be:
1: Use the IF statement within the NETWORKDAYS function to adjust your start date...
=NETWORKDAYS(IF(AND(VALUE(MID(Created@row, FIND(" ", Created@row) + 1, FIND(":", Created@row)  FIND(" ", Created@row)  1)) > 6, FIND("P", Created@row) > 0), DATEONLY(Created@row) + 1, DATEONLY(Created@row)), [Completion Date]@row)
.
2: Run an "either/or" NETWORKDAYS function based on the result of the IF statement...
=IF(AND(VALUE(MID(Created@row, FIND(" ", Created@row) + 1, FIND(":", Created@row)  FIND(" ", Created@row)  1)) > 6, FIND("P", Created@row) > 0), NETWORKDAYS(Created@row, [Completion Date]@row)  1, NETWORKDAYS(Created@row, [Completion Date]@row))
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.4K Get Help
 364 Global Discussions
 202 Industry Talk
 430 Announcements
 4.4K Ideas & Feature Requests
 137 Brandfolder
 129 Just for fun
 128 Community Job Board
 446 Show & Tell
 28 Member Spotlight
 1 SmartStories
 284 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!