Help

Re: Automate due dates based on days of the week

312 0
cancel
Showing results for 
Search instead for 
Did you mean: 
MichelleS
4 - Data Explorer
4 - Data Explorer

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!

1 Reply 1
Arthur_Tutt
8 - Airtable Astronomer
8 - Airtable Astronomer

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:

Screenshot 2023-10-30 221516.png

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:

 

Screenshot 2023-10-30 221748.png

 

Automation Input Variables:

Screenshot 2023-10-30 221824.png

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!!