Formula not working consistently

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
    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.


    Jeff Reisman, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!