Status formula based on date

Brett L

Looking for assistance, and seems like the multiple variations I have tried are not producing the results. Here is what I am seeking accomplish:

[CRM Status] Cell for Results

If [74. Discharge date]@row is blank return "No Discharge"

If [74. Discharge date]@row is TODAY or equal to / less than 3 days from TODAY return “OK”

If [74. Discharge date]@row is more than 3 days from TODAY return “NEED”

IF[CRM Complete]@ row is checked return “OK”

Thank you in advance for your assistance!!!

Danielle Arteaga

Give this a try. You can place it in your CRM Status column as a column-level formula. NOTE: You may need to modify the column names below to match your worksheet columns

=IF(ISBLANK([Discharge Date]@row), "No Discharge", IF(OR([Discharge Date]@row <= TODAY(-3), [CRM Complete]@row = 1), "OK", "NEED"))

Brett L

Thank you for the quick response! So close to what I needing...

After further analysis and trying, it appears that it may be more like this:

=IF([74. Discharge Date]@row < TODAY(-3), “Need”

IF(OR([74. Discharge Date]@row = >TODAY(), [74. Discharge Date]@row < = TODAY(+3), [CRM Complete]@row = 1), "OK",

 “”) 

If the date is more than 3 days from discharge = need

If in between discharge and 3 days, or CRM Complete = OK

IF none of these = blank

Additional help would be awesome--soooo, close! 😀

Genevieve P.

Hi @Brett L

Were you able to get the formula you need?

One thing to note is that in the text above I can see curved quotes like this:  “”

Smartsheet will need the formula to have quotes that are straight up and down, like so: ""

This means you'd need to adjust the formula to have the right type of quotes (by typing it directly into the cell). Try this:

=IF([74. Discharge Date]@row < TODAY(-3), "Need", IF(OR([74. Discharge Date]@row = >TODAY(), [74. Discharge Date]@row < = TODAY(+3), [CRM Complete]@row = 1), "OK", ""))

Let me know if changing the quotes worked for you!

Cheers,

Genevieve