Is there a way to customize rules for duration calculations?

Laura Shear

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)

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.