I fixed my issue but why? - Notification email delayed by 1 day

Mr J

I have a sheet that notifies people when they need to review a document based on when it reaches its expiration date. The idea is that people are sent an email notification 90 days, 60/30, and 7 days before it expires so they can take action.

I have the date the document expires in one column and in another column, I have the days count down.

1) Days until .png

When the day countdown hits 90 for example, it triggers the status cell to change to 90 days, and that triggers an email.

Renewal deadline status.png

The email would say the document expires in 90/60/30 or 7 days and the email pulls the numbers of days until it expires from the column that shows the days until expiration.

I tried various options, but the email would always go out the day after, so the email would say the document expires in 89 days or 29 days etc. The only fix I could find was to change the trigger to happen on the 91st day or 31st day. This solved the issue but seems wrong, and I want to understand why so I can troubleshoot in the future.

Would love some insight.

1) Days until .png

2) Trigger to change cell value.png

3) Message Trigger.png

1.5) Days until Governing deadline formula.png

Paul Newcome

Do you have any method in place to update the TODAY function? The TODAY function does not update on its own. The sheet has to be activated and saved first. Usually I just insert a hidden date type column and use a Record A Date automation to record the date in this hidden date column on a daily basis at 12:00am.

Mr J

Thanks for writing back. I'm not sure I follow.
The sheet is active and the formula and automations are working, they are just one day off. When it should be doing something on the 30th day, it is doing it on the 29th day. The only way around it that I can find is by telling it to action on the 31st day and that way it does it correctly on the 30th. Which works but is odd and I'd like to know why so I can fix it properly.

Georgie

Hi @Mr J,

To add to Paul’s advice, it might be that the TODAY function is being updated later than the workflow runs. 

I can see that your Change Cell Value workflow is set to run every day at 4am PST. The TODAY function is only updated in certain instances, such as when the sheet is opened and saved. For a full list of the actions that update the function, check out the Usage notes in the help article here: TODAY Function.  

So, it might be that the automation is running before the TODAY function has updated each day. To ensure that the TODAY function is updated to the correct date every day before the Change Cell Value workflow runs, you could set up a Record a Date automation, as Paul suggested, and set that workflow to run prior to your Change Cell Value workflow - for example, at 2am PST. For the steps to create that workflow, take a look at option 1 in this help article: Automatically update the TODAY function in formulas.

Hope that answers your question!

Georgie