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

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    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.

    PMP Certified

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!