Monthly Automation report sends out last months data

David Klockzien

I have a simple automation that looks at an employee sheet. On the 1st day of each month at 10am Central a report is sent to my email that SHOULD show the current months work anniversaries. However, every month I receive the email on the first of the month and it shows the PRIOR month anniversaries.

Within the sheet I am using the following formula in a column labeled Anniversary Month. Based on this the column should change to yes at 12:01AM on the first day of each month. When I open the sheet the Anniversary Month shows YES or NO accordingly.

=IF(MONTH(TODAY()) = MONTH([Hire Date]@row), "YES", "NO")

The automation simply looks for "Yes" in the Anniversary Month Column and should send me the Names and hire dates of each person.

Anyone know why the report sends prior months data? Does the sheet Anniversary Month column only update when the sheet is opened? How can I resolve this issue as I do not wat to open the sheet each month for the correct data to send.

Maaik Meijerink

Hello @David Klockzien ,

Yes indeed, the function TODAY() only updates when something in the sheet changes and therefor someone must open the sheet.

However what I do to overcome this problem is using the Record a Date Automation Workflow of course triggered on a 'When a date is reached' - trigger set to Repeat every 1 day. This way every day the current date can be stored on every line, making it possible for you to let your current process work or rewrite your current trigger to embed the described logic.

David Klockzien

Maaik,

Thanks for the work around. I will give it a try.

Just wish there was a trigger that would activate worksheet formulas without the sheet open.

Have a great day!

AMB_L

I use a Today helper column to keep things from going out of date. There are a few threads that cover this and are helpful, such as:

https://community.smartsheet.com/discussion/75798/keeping-today-up-to-date-even-when-the-sheet-is-closed

MPath

To anyone who might end up here after dr.google.

You could also have automation lock rows at 1 in the morning and unlck an hour later. This keeps the sheet 'alive' too.