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?
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
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.
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.