Hello Community,
I set up an automation that is “When Start Date changes, adjust Due Date by the number of days in Timeframe”. This is working great as each task takes a different amount of days to complete, but the issue is, it’s registering weekends (which I don’t want). Does anyone know a way around this so that the automation doesn’t include weekends?
For example, my start date for the task is Dec. 23 (Monday), the time frame is 5 days, and the Due Date is registering as Dec. 28 (Saturday). Is there a way to make it 5 Business Days, aka the Due Date would be read as Dec. 30 (Monday)?
I don’t think this ia available, at least not straight forward. I would be nice if you cat set unavailable days at the account level (eg. weekend days, public and bank holidays). If that is made available the next step is to develop formulas that takes this into account, like MoveWorkDays(nr. of days).
So i did get confirmation that they haven’t made this available yet - I did create this formula that pulls from the timeframe to kind of do this - the issue is that if a date falls on a friday, the formula gets skewed. Dropped it here to see if anyone could figure it out
FORMAT_DATE(
ADD_DAYS(
ADD_DAYS({Start Date},{Timeframe}-1),
IF(({Timeframe} <= (WORKDAYS(ADD_DAYS({Start Date},{Timeframe}-1), {Start Date}))),0,
IF(({Timeframe} <= (WORKDAYS(ADD_DAYS({Start Date},{Timeframe}), {Start Date}))),1,
IF(({Timeframe} <= (WORKDAYS(ADD_DAYS({Start Date},{Timeframe}+1), {Start Date}))),2,
IF(({Timeframe} <= (WORKDAYS(ADD_DAYS({Start Date},{Timeframe}+2), {Start Date}))),3,
IF(({Timeframe} <= (WORKDAYS(ADD_DAYS({Start Date},{Timeframe}+3), {Start Date}))),4,
IF(({Timeframe} <= (WORKDAYS(ADD_DAYS({Start Date},{Timeframe}+4), {Start Date}))),5,6) )))))))
RIGHT(LEFT(CONCATENATE(WORKDAY({Start Date}, ({TimeFrame}+1))), 15), 11)
(I added the “+1” because if I use to set my deliveries to the mornings, therefore, I add “one” day, but you can delete it, if you need)
However, they should come up with a solution as @basdebruin suggested, because using this formula loses all the porpuse of having a due date column. Once date columns can be linked to status columns, but formulas can not.
This worked great! Thank you so much for this! I’m hoping they do fix this soon - I just added an additional “Due Date” column and just copied the date that gets automatically formulated - seems redundant but need to be able to link the date to status.
I’m doing the exactly same thing, I even tried to use a formula that counts the day between the correct due date and the start date, and them automate it, but again… formulas are not allowed on automations
Happy to help