Automate due dates based on days of the week

[MichelleS]

Hi! I'm trying to create an automation to assign a due dates to tasks that all have a due date of Thursday, and the tasks vary by how many weeks out they should be completed. For example, our "Final Paperwork Due" task always needs to be due the Thursday 2 weeks out from the event date.

I've been able to make a formula that can automate the deadline to a certain number of days out from the event, but I can't pin down how to always make the due date a certain day of the week. Any help appreciated!

[Arthur_Tutt]

Hey @MichelleS ! At first I thought this would be super simple, but ended up being slightly more complicated than I thought, but cracked the code! I recorded a quick Loom video to take you through step by step how I set it up. 

Remember when setting this up for yourself to update the Table names / fields / variables to match your data exactly to make it work. 

Table Setup:

Due Week Formula:

DATEADD(
  {Date of Event}, {Weeks Until Due}, 'weeks'
)

Due Date Formula:

DATEADD({Due Week}, {Extra Days}, 'days')

Day Of Week Formula:

DATETIME_FORMAT( {Due Date}, 'dddd' )

"Is It Thursday?" Formula:

IF(
  OR({Extra Days} = -1, NOT({Weeks Until Due})),
    "n/a",
  IF(
    {Day Of Week} != "Thursday",
  "No", "Yes"
))

Automation Setup:

Automation Input Variables:

Automation Script:

var assignementTable = base.getTable("Assignments");

var inputConfig = input.config();
var recordID = inputConfig.recordID;
var extraDays = inputConfig.extraDays;

var addExtraDay = extraDays + 1;



// Temp Creating Fake Data (To Allow Automation To Retrigger)
var updates1 = [{
  "id": recordID,
        fields: {
            "Extra Days": -1
        }
}]

await assignementTable.updateRecordsAsync(updates1);

// Updating Due Date With New Date
var updates2 = [{
  "id": recordID,
        fields: {
            "Extra Days": addExtraDay
        }
}]
console.log(updates2)

await assignementTable.updateRecordsAsync(updates2);

 

Let me know if this worked for you!!