Solution: Add Duration in Business Days to Start Date

Showcase Tips and Hacks
12

@Laurent_Auneau I got a kick out of this brain teaser and implemented two algorithms (and even discovered a trick along the way).

Here’s a while loop based algorithm. This is something a traditional coder would do. Essentially it’s this:

current date = start date
sum of days = 0
while (sum of days < required days)
  availability for this day = rules.filter(
    type = "exact date" and date = current date
    OR type = "weekday" and weekday = [current date].weekday()
  ).availability minimum of all matched rules
  sum of days = sum of days + availability for this day
  current date += 1
)
return current date

— i.e. keep checking for availability for the next day until the sum of available days satisfies task duration.

Implementing a WHILE loop in Coda live formulas is not supported normally. But I wouldn’t be Paul if I didn’t find a way to force Coda into recalculation mode until a condition was met. It’s not super reliable with task reordering, but the implementation is here:

However, later I came up with a different algorithm. This one doesn’t require a WHILE algorithm and doesn’t use any hacks — instead it’s not as trivial to explain. The idea is to estimate an end date somewhere on or after the actual end date by applying all availability rules in their strictest forms. This would give us a date that’s not before an actual one. After that we can test each date against the rules using Sequence().FormulaMap() and this outer CurrentValue access trick and find out how many days would be enough to actually satisfy the requirement.

The implementation is here. It’s much faster and more stable too:

3 Likes