You can do this using a few formulas instead of an automation if you’d like.
Here is a formula that could change the date (we’ll call it Calculated Date):
IF({Due date field}="",{Due date field},DATEADD({Due date},
SWITCH(WEEKDAY({Due date field}), 0, -2, 6, -1, 0),'days'))
If you’d like to do it with automation to update the existing due date field automatically, you could do it a few ways:
With a helper column in your table using the field you just created above and one additional helper column (we’ll call this Date Check)
IF({Due date field}={Calculated date},"Yes","No")
- Create an automation to check “When a record matches a condition.”
- Select the table
- Add a condition that checks if your Date Check field is equal to “No”.
- Select an action to Update Record
- Choose your table (same as above)
- Select your record ID from step 1
- Choose your Due date field
- Set the field to be dynamic by clicking on the gear icon to the right
- Choose your Calculated date field
The benefits to using the additional columns (3 total: Your original Due Date, Calculated Date, & Date Check) is that it limits the need for addition views that an automation that would be dependent on it to run successfully. Additionally, this achieves the same process if you wanted to use a script, just a bit easier.
Hopefully that helps!
