Date Arithmetic

[Sean_Murphy1]

Hello all,

How do I add time to a Date field using the scripting block in automations? I know about DATEADD() in formula fields but I’m trying to iterate through a loop. I want to automatically create an indeterminate number of new records where each record’s Date field is the next day (19th, 20th, 21st, etc) in other words, n = n + 1day.

Is there some way I can do this in my script’s for loop? Or some clever way to do it with formula fields?

Thanks,

[Alexey_Gusev]

you would better use autonumber field+DATEADD. date operations in JS5 are not so convenient as other parts,

[Justin_Barrett · Accepted solution]

Alexey_Gusev:

date operations in JS5 are not so convenient as other parts,

Do you mean ES5?

Thankfully Airtable’s scripting environments support ES6. While date calculations still take some getting used to, they’re not that difficult for basic things.

@Sean_Murphy1 The formula solution that @Alexey_Gusev mentioned will definitely do the job. If you still want to try a scripting option, the Date instance methods that you’ll need are getDate() and setDate(). Here’s an example that creates a new Date instance with the current date (the default when no date info is passed during instantiation), and then adds one to the date.

const date = new Date()
console.log(`The starting date is ${date.getDate()}`)
date.setDate(date.getDate() + 1)
console.log(`Now the date is ${date.getDate()}`)

In case you’re wondering, this process of adding to the previous date works just fine when the new date crosses a month boundary, with the Date instance automatically adjusting the month as necessary. For example, if the date before the shift is the 31st of January, the date after the shift will be the 1st of February.

[kuovonne]

Since you want to create an indeterminate number of new records with consecutive dates, you cannot do this with formula fields and a single automation run. There are clever ways of doing this with multiple automation runs and formula fields, but those tend to require additional fields that can clutter up a base.

What is your level of experience with scripting?

Is there a date field with the starting date?

1. Read the date value from the triggering record. It will be an ISO text string.
2. Convert that text string into a JavaScript date object using new Date().
3. Find the next date with a combination of .getDate() and .setDate() methods on the date object.

[Alexey_Gusev]

Do you mean ES5?

Sorry, I had to type JS. But I can totally agree that ES6 changed a lot, even make programming enjoyable.
OOP is also new thing for me, still can’t be used to a fact that I need to create object at first, in order to use it’s methods.

[Justin_Barrett]

Alexey_Gusev:

still can’t be used to a fact that I need to create object at first, in order to use it’s methods.

Not always. There are many methods that can be used directly on a literal instance of an object type; e.g. testing to see if a literal array includes a string pulled from a record:

if (["Region 2", "Region 7"].includes(rec.getCellValueAsString("Region"))
    output.text("Region is restricted")