Auto Update of today() or now() formula

Hi guys,

I have a formula that I want to update automatically if a condition is met -
IF({U/C Date}=TODAY(), “yes”, “no”)

Based on the answer to that formula being “yes” I am trying to use integromat to send an email.

It is my understanding that the today() function will not update until the base is reloaded or a record is edited, therefore my question is - can I use zapier or integromat to update the base on a schedule each day and therefore have the today() function update without the need for a human to open Airtable?

Any suggestions would be really helpful.

Thanks,

Welcome to the community, @Theo_Field! :smiley:

Probably, though it’s kind of redundant. If you build this in Integromat, for example, you’re running a scenario to force-refresh Airtable, which would then lead to another Integromat scenario being triggered to send the email.

It would be cleaner to just have a single Integromat scenario set up to run once a day and send emails based on records found where the {U/C Date} value matches today’s date. The scenario would begin with a “Search records” action, using a filter formula of {U/C Date}=TODAY(). The rest of the scenario would be set up to send the email as you wish.

Thanks @Justin_Barrett - I wasn’t sure if it would still catch the match if the sheet hadn’t been updated!

If I set up the integromat scenario as suggested, will I need to worry about time zones? I am in New Zealand and I’m having some issues in Airtable with the today() function matching to the {U/C Date} field.

When using the search action in Integromat, it always captures Airtable data in its most updated state.

Airtable’s TODAY() function uses GMT for comparison. What you might try is making a formula field named something like {Today Local} that manually shifts TODAY() based on your offset from GMT. From what I see, New Zealand is GMT+12, so try this for {Today Local} :

DATEADD(TODAY(), 12, "hours")

Then in Integromat, you’d set up your Airtable search action to use the following filter:

{U/C Date}={Today Local}

FWIW, if you ever find yourself using Integromat’s own “today” option in a field calculation, make sure that you’ve set your timezone in Integromat’s settings.

Thank you for your suggestions @Justin_Barrett - I think I am getting somewhere based on what you suggested, however I was still having some issues with the dates matching and allowing the Integromat scenario to run.

However I am testing using the native today() within Integromat - it looks like it will read the date in the {U/C Date} field and compare it to my local date (set within my profile settings).

The other option I was testing was to extract the day as in integer using Day() from both fields and then using {U/C Day}={Local Day} in Integromat. That may be a little more complicated than needed, but it seemed to be working.

1 Like