Auto Reporting for Prior Month Using Today Function

Jackie Bhamornsiri

Hi. I'm looking for guidance. My smartsheet tracks activity for our team. On the 1st day of the month, I have a report that captures any activity that occurred in the prior month based on the following formula.

=IFERROR(IF(AND(MONTH([Last Touched]@row) = MONTH(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1), YEAR([Last Touched]@row) = YEAR(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1)), "Previous Month", "-"), "-")

The report is set to run at 7am EST (my time zone) and was sent at 7:17 am EST. However, the 1/1/25 auto report erroneously contained data from Nov-2024 when it should have Dec-2024.

Looking into the issue further, I discovered that my time zone was incorrectly set to PST, which I corrected. However, if the time zone were the only problem, the report should have still contained Dec-2024 data.

When I view the report now, it contains Dec-2024 data. So I'm baffled.

I guess my question is this…Should I expect a delay in reporting on the 1st of the month or 1st of the year? Is there a problem with my use of the today function? Or is there something else that could have caused the delay?

I manually sent the report in the past without any issues, but I didn't normally send it out until later in the morning.

Please advise.

Paul Newcome

Do you have anything set up on the sheet to automatically update the TODAY function in the background?

Jackie Bhamornsiri

Hi Paul.

No. I'm a newbie that is frankensteining things together (for now). Does it not just update on it's own? Do you have any suggestions? Do I need to add automation to do this?

Paul Newcome

Unfortunately the TODAY function does not update on its own. It only updates when the sheet is activated. There are a number of ways to do this, but my preferred method is to insert a date type column (I usually call it "zzzTODAYzzz") and then set up a record a date automation triggered daily to record the date in this zzzTODAYzzz column. That will force refresh the TODAY function on your sheet.

Jackie Bhamornsiri

Got it! Thank you, Paul!

Paul Newcome

Happy to help. 👍️