Solution: Add Duration in Business Days to Start Date

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!

3 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

1 Like

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 :slight_smile:

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.

1 Like

Ah! Love learning something new. Good find.