How to perform a date Validation referencing other fields?

LARRY MCNULTY

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:

Kelly Moore

Hello Larry

If you're only wanting to notify and/or alert the PM, you may be able to achieve this without a formula. You could highlight the cell with color but without text using conditional formatting.

Using automation based on when-a-row-is-changed, you can send a notice to the PM when your criteria are met. If your automation is an Update request, the PM could have the opportunity to enter the dates (and completion status if desired) directly into the request.

If you really wanted to display the text, you will need an additional column. As you saw in your current formula, you cannot place the formula in the same cell you are referencing. Also, you cannot mix formula and direct entry without interrupting the auto-fill of a formula down a column as soon as someone directly enters a result in that column.

You could, however, with a single column that would house the formula and would only display text, direct the user to enter the appropriate date in the other column. For instance "Enter Start date in Start-Date column" or "Enter End date in End Date column". Your formula above should work in this helper column.

Depending upon your desired solution, we can help you with any instruction you may need.

Let us know.

Kelly Moore

Hello Larry

If you're only wanting to notify and/or alert the PM, you may be able to achieve this without a formula. You could highlight the cell with color but without text using conditional formatting.

Using automation based on when-a-row-is-changed, you can send a notice to the PM when your criteria are met. If your automation is an Update request, the PM could have the opportunity to enter the dates (and completion status if desired) directly into the request.

If you really wanted to display the text, you will need an additional column. As you saw in your current formula, you cannot place the formula in the same cell you are referencing. Also, you cannot mix formula and direct entry without interrupting the auto-fill of a formula down a column as soon as someone directly enters a result in that column.

You could, however, with a single column that would house the formula and would only display text, direct the user to enter the appropriate date in the other column. For instance "Enter Start date in Start-Date column" or "Enter End date in End Date column". Your formula above should work in this helper column.

Depending upon your desired solution, we can help you with any instruction you may need.

Let us know.

LARRY MCNULTY

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