Add duration to tasks that overlap holiday

[Kit_Kollmeyer]

I have set up a table that generates a task start date based on a prerequisite task. It then makes the task end date based on the duration in days that are entered for the task. This is great for stacking sequential tasks. The issue I’m not solving on my own is how to automatically add days to that duration based on holidays. I’ve entered the holiday as a task with a start date and duration and end date. Is there a formula that would say 'If task end date (or start date) is between holiday start date and holiday end date, then add (difference between task end date (or start date) and holiday start date) + (difference between task end date (or start date) and holiday end date). If I can get that logic into a formula based on those columns, it will essentially add the missing work days to the task length.

Do any of your Coda masters have suggested formulas?

[Scott_Collier-Weir]

Hey there!

This should help you out - I didn’t mimic your doc directly (with pre-reqs and all), but I did replicate the addition to duration with holidays function!

Let me know if it helps out or if you are looking for a different output

[Christiaan_Huizer]

great solutions of @Scott_Collier-Weir

but there is no need for FormulaMap() in this construction, the filter does the job.

Sequence(thisRow.[Start Date], thisRow.[Start Date] + Days(thisRow.Duration))
  .Filter(
    CurrentValue.In(Holidays.Date)
  )
  .Count()

Cheers, Christiaan

[Scott_Collier-Weir]

Woah - Yeah. No need for formulaMap and converting the number back to a date. Do you know why that works @Christiaan_Huizer ?

Shouldn’t the in() not work because you are trying to compare two different data types (date and number). Does the filter() itself do the conversion under the hood?

[Christiaan_Huizer]

hi @Scott_Collier-Weir , what I see is that the function sequence() generates a list of numbers (dates are numbers) and you filter on this list by comparing each item (the CurrentValue) with the holiday date (which is a number as well). ‘if true’ you count. Something like this? Cheers, Christiaan

[Paul_Danyliuk]

@Christiaan_Huizer, @Scott_Collier-Weir — folks, last time I checked, Workday() accepted the list of holidays to jump over as the third param.

So the correct answer would be:

Workday(Start date, Duration, List(HD1, HD2, ...))

where the list can, of course, be taken from anywhere, e.g. a column on an external table:

You’ll have to be careful with durations though: 7 days would now mean “7 work days” exactly, i.e. Workday() would automatically jump over the weekends for you.

I used to solve a more complicated variant of this some time ago. It had flexible availability rules, e.g. custom days off, half-days and so on. The post also explains why the algo to add duration and then add the number of holidays within that range won’t work: the days that the new end date falls on can also be holidays, so you’d have to shift the end date further, which you cannot do without a while loop or a different algo.

Solution: Add Duration in Business Days to Start Date

@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…

Cheers, and welcome to the community, @Kit_Kollmeyer !

---

P.S. @Scott_Collier-Weir, in Coda dates are basically numbers. Those are 1/1/1900-based indices like in Excel, i.e. each date is stored as the number of days since 1/1/1900 inclusively. And fractions represent time of the day. That’s why you can interchangeably compare dates and numbers and do math and make sequences on dates.

[Scott_Collier-Weir]

Workday() was provided as option 4 on my doc embedded above!

Wanted to give options though to account for how @Kit_Kollmeyer seemed to already be assigning duration (not skipping weekends), hence the multiple options.

And thanks for the explanation about dates as numbers - totally makes sense and makes things easier in the future!

[Paul_Danyliuk]

Oh, sorry, was reading through an explanation and only saw holidays mentioned in Option 2 or so.

Workdays() is the only of the bunch that would work reliably though (i.e. slide end date further if those are the holiday season) so I’d still advise the OP to use that one.

[Kit_Kollmeyer]

Wow, thanks so much everyone, this is super helpful!

[Christiaan_Huizer]

hi @Kit_Kollmeyer , it was @Scott_Collier-Weir who provided the solution, would you be so kind to mention his contribution as solution in order to close this thread? Thank you!

[Kit_Kollmeyer]

@Scott_Collier-Weir thank you so much for this solution!