How to perform a date Validation referencing other fields?


Hi Smartsheet Community,

I don't know why I'm struggling with this formula and hoping you can point out to me what I/m doing wrong.

In summay what I'm trying to accomplish.

When the status column is equal to Complete and the actual start and / or end dates are blank I'd like to either Highlight that column cell, or enter a message letting the PM know to enter the dates. Looking for automation and formulas to make the PM aware that when a task is complete to update the actual dates.

Could you please advise me in what I'm doing wrong? Thanks

Formulas that I've tried:

=IF(Status@row = "Complete", IF(ISBLANK([Actual Start Date]@row), "Need Date", "")) <-- Circular Reference

=IF(ISBLANK([Forecast End Date]@row), IF(Status@row = "Complete", "Need Date", "")) <-- this works but referencing a different date and writing back to the Actual Start Date column. But I want the Actual dates populated.

Sample Sheet of plan and columns below:

Best Answer



    Thanks for your assistance. I figured i'd use the highlighted route as this was my second choice.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!