Hey Coda community!
A customer wrote in asking if there were a way to add a duration in days to a start date and have the end date reflect the difference from the start date as if the duration were only business days.
If my duration in days were 8, I would return the date 8 business days from the start date. It follows that the solution shouldn’t return a weekend date, either.
My solution is below. It doesn’t take into account holidays. Would love to hear if you have a more elegant one / a solution that accounts for holidays!
4 Likes
Hey @BenDavis I believe NetWorkingDays() was designed for this purpose. Makes it very easy to work with business days vs calendar days.
Hi @Johg_Ananda
NetWorkingDays() is indeed great for calculating the number of working days between two dates.
What the doc I posted above does is kind of the inverse. It takes N working days as input and spits out the calendar date that is N working days in the future.
-Ben
2 Likes
Hey @BenDavis,
Sorry, but the calculation is incorrect. E.g., Friday to Monday (inclusively) is two days, not three, and Monday to Monday (inclusively) is 6 days, not 5.
Fixed the calculation, and changed/split the formula to hopefully be a bit clearer. First I determine the number of weekends that must be jumped over:
RoundDown((thisRow.[Duration (in business days)] + thisRow.[Start Date].Weekday() - 3) / 5)
Logic:
- Number of weekends = how many full times duration is strictly larger than 5. Hence (duration - 1) / 5…
- …but that would be true if we counted since Monday. So account for the fact that StartDay may land on another work day by adding
StartDate.Weekday() - 2 (because Monday is 2)
Then I add that to the input number, and subtract 1 because we’re counting days inclusively.
Full formula would be:
thisRow.[Start Date] + Days(
thisRow.[Duration (in business days)]
+ RoundDown((thisRow.[Duration (in business days)] + thisRow.[Start Date].Weekday() - 3) / 5) * 2
- 1
)
It will break if start date is not in Mon-Fri range though. But handling invalid input is left for the reader as an exercise 
Demo:
1 Like
Nice solution! I wasn’t trying to count days inclusively so this handles that situation well. Thanks Paul.
Just found out there was a formula for adding business days all along — there was no need to sweat out our solutions @BenDavis
Workday(Start date, Number of workdays to add, Optional list of holidays)
Not the best naming for this function tbh — AddWorkdays() would’ve been better. Guess that’s why it slipped under our radars.
3 Likes
Ah! Love learning something new. Good find.
This might be the stupidest question, but how does one make this conditional? ie. the start date is based on the current date/date of record entry. I can only seem to get this formula to work if the state in the formula is static.
Hi @anon36554742,
welcome to Coda Community! 
What do you mean with static?
The Start Date parameter of the formula could come from any source (a cell, a date picker).
Could you explain your use case to better understand the scenario?
Thank you.
Hey everyone,
with my task management document, one of the things I’m trying to achieve is to know up to when someone is busy.
- tasks have a duration in days, with possible decimals (eg. 1.5 days)
- and people can be working only 3.5 days a week, sometimes more or less depending on weeks (I keep tab of this along with holidays)
So the easy way is to use the Workday formula, with Today() as a start date, throw in the sum of task efforts assigned to a person, and for days off … it gets much more complicated.
First, for the sake of posterity, let me post this formula that may help others at some point:
It takes the first day of an employee leave, the duration of this leave, and transforms it into a list of days that you can feed to the “Holidays” input variable. A bit of combine with the official days off list, and I thought I was good to go.
Up to the point where I realized it doesn’t take into account half days. So… I guess all your work was not for nothing. I had to improve a bit the formula so that it handles float durations:
Going to integrate this tomorrow hopefully. Will let you know if all this works out!
-L
2 Likes
Really interesting, @Laurent_Auneau! Let us know how it goes.
@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
