Date Based Automation Not Working

We have a sheet to help track when notary renewals are upcoming for staff and its a fairly simple process. We have a date column that lists the expiration date, a helper column that turns that into a total day count, status, and if an application was recvied.

The trigger for this would be when that Days Until Expiration column hits 45 based on the date in the Expiration column compared to todays date AND if the App Received column is unchecked, it should send an email.

I included screenshots of our columns and the automation below.

But we can't get the automation to function and send emails as it should. Any ideas??

Find more posts tagged with

Sheets

Automations

Accepted answers

Paul Newcome

Ok. I would suggest inserting a date type column (called "Today's Date" in this example). This can be hidden after setting up. Then you would create a record a date automation set to run daily at 12:00am that records the date in the [Today's Date] column.

You would still use the TODAY() function, but this daily automation will update the back-end date every day regardless of whether or not the sheet is opened. This will keep your [Days Until Expiration] column up to date which in turn should help your alert automation run.

All comments

Paul Newcome

Do you have a formula in either of the two columns? If so what are they?

Ashley Klaers

No formula in the Expiration Date column as thats entered from the submitter application for their next expiration date.

The Days Until Expiration column has the following formula:

=[Expiration Date]@row - TODAY()

So I know as of today its 45 days until my test expiration date on 3/27/26

Paul Newcome

Do you have anything in the sheet that activates it daily such as a different automation that changes cell data, someone that opens it daily, daily update requests, etc.?

The TODAY function does not update the date on the back-end until the sheet is actually activated. So if it hadn't been activated before the automation ran, the formula may not have been outputting "45".

Ashley Klaers

Our other automations are meant to send alerts based on some of the other checkboxes or date.

There is a formula in the Status column that adjusts the status depending on the day count in Days Until Expiration:

=IF([Days Until Expiration]@row < 0, "Expired", IF([Days Until Expiration]@row <= 30, "Urgent", IF([Days Until Expiration]@row <= 60, "Upcoming", "OK")))

Otherwise I'm not sure if someone comes in daily to make changes but I know some checkboxes will be marked off when an application continues further in the process, heres a full screen with all the columns

Paul Newcome

Ashley Klaers

I'll have them make these changes and see how it turns out.

Your comment on the TODAY() function, are you saying to add that to the "Todays Date" column we'll add or are you referring to how its being used in the Days Until Expiration column?

Paul Newcome

How it is being used in the [Days Until Expiration] column.

You would leave the [Today's Date] column empty. It should get filled in automatically every day at 12:00am(ish) with the current date. That's why I say you can hide it after setting it up. There should be no reason to interact with it after it has been created. The automation should handle that.

Ashley Klaers

Its subtracting TODAY() from the Expiration date to basically get the countdown.

But I figured out your explanation tot eh new column and have that set up with the automation so I'll let tomorrow come around to see if it triggers.

Ashley Klaers

This method did end up working and autommations went out today as intended so I think we're all good. Thanks for your help Paul!

Paul Newcome

Happy to help. 👍️

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.