Calculate Assigned Date Based on Drop Down Column Values and Due Date


In my Smartsheet I have a "Severity" drop down column with the following options: Critical, High, Medium, Low. There is a "Due Date" column that is filled in. I need a formula to populate an "Assigned Date" column as follows:

-If "Severity" is "Critical" or "High", "Assigned Date" will be 30 calendar days prior to "Due Date"

-If "Severity" is "Medium" or "Low", "Assigned Date" will be 90 calendar days prior to "Due Date"

I would greatly appreciate any help!

Thank you!


Best Answer

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭
    Answer ✓

    Give this a try:

    =IF(ISDATE([Due Date]@row), IF(OR([Severity]@row = "Critical", [Severity]@row = "High"), [Due Date]@row - 30, IF(OR([Severity]@row = "Medium", [Severity]@row = "Low"), [Due Date]@row - 90, "")))   


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!