Formula not working consistently

Hollie Green

Below are the formulas I am using to get the follow up date to automatically calculate

Final row shows incorrect date at times and sends Automation reminder a day early. The sheet is opened everyday Monday through Friday and has changed while the sheet was open from the correct date to a day early and back to the correct date. Any ideas how to prevent this or what is causing it?

=IFERROR(IF(AND([Evaluation #]@row = "2", [Monitor Type]@row = "CPA"), INDEX(COLLECT([Follow Up Date]:[Follow Up Date], [Oracle ID]:[Oracle ID], [Oracle ID]@row, [Evaluation #]:[Evaluation #], "1", [Monitor Type]:[Monitor Type], "CPA"), 1), ""), "")

Follow Up Date Formula

=IFERROR(IF(AND([Evaluation #]@row = "1", [Monitor Type]@row = "CPA"), WORKDAY([Test completion]@row, 5), ""), "")

Test Completion that is referenced the follow up date formula

=IFERROR(IF([Monitor Type]@row = "CPA", INDEX(COLLECT({Created}, {Month}, Month#, {Oracle ID}, [Oracle ID]@row, {Audit #}, [Evaluation #]@row), 1), ""), "")

Jeff Reisman

@Hollie Green

I think the issue is with referencing the Created date system column. I believe Smartsheet stores Created date and Modified date values in UTC, which then gets converted to your regional setting time zone at the presentation layer. Sometimes this conversion can lag, or hits some issue, and changes back and forth - which can and does throw off date calculations and automations.

But don't despair, there is a workaround!

Add a date column called something like "Date Created" and then add an automation that is triggered when a row is added, which records the current date in the Date Created column. I believe this date value matches the regional settings time zone of whomever owns the sheet, so if you're in EST and a row is created at 11pm on 12/22/2022, that's the date that is recorded, not the current date in UTC, which would already be 12/23/2022. Use this date column to set your Test Completion value.