Is there a way to customize rules for duration calculations?
I'm using Smartsheet to manage contracts and their critical dates. The critical dates of these contracts are typically written with a rule set that any duration given with 5 or less days is to be counted as business days (weekends are excluded from duration count, and if the end date lands on a holiday then it moves to the next business day), and any duration given with 11 or more days are counted as calendar days (weekends and holidays included in duration, unless end date lands on a weekend or holiday, in that case it would be moved to the next business day). In all cases, the start date is excluded from the count. Is there any way to create rules within smartsheet that would accommodate the above, or is that too much for SS? I'll give the following examples of what I'd like to see, working around the Nov 11, 2021 Holiday (Veteran's Day).
Start Date Duration End Date that I would like to see in Smartsheet
11/8/21 1d 11/9/21 (right now SS shows me 11/8/21)
11/8/21 3d 11/12/21 (would like it to skip the 11/11 Holiday - ONLY IF END DATE LANDS ON IT)
11/8/21 4d 11/12/21 (would like it to skip the 11/11 Holiday - ONLY IF END DATE LANDS ON IT)
11/8/21 7d 11/15/21 (count calendar days only)
11/8/21 30d 12/8/21 (count calendar days only)
11/8/21 33d 12/13/21 (count calendar days, but since it landed on a weekend, it pushed the result to the next business day, 12/13/21)
Answers
-
Hi @Laura Shear
Hope you are fine, yes you can do that by using a helper column with functions like WEEKDAY & NETWORKDAYS to define your conditions and create the suitable formula by each case. if you like i can help you to create this application but i need some information and sample date. please Email me.
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!