Conditional Formatting to add days to date based on risk category

Options
Dami
Dami ✭✭
edited 03/16/23 in Smartsheet Basics

Please help. I am trying to work with two columns:

A Risk Category column which consists of High, Medium, and Low categories;.

And a Committed Closure Date column which, depending on the risk category, is the date by which the gaps must be closed.

Here is what I have which has not worked. The columns are already of the appropriate data types. Please help!!!

=IF ([Risk Rating]@row ="High", [Committed Closure Date]@row = TODAY(15), 

IF ([Risk Rating]@row ="Medium", [Committed Closure Date]@row = TODAY(45),

IF ([Risk Rating]@row ="Low", [Committed Closure Date]@row = TODAY(60))))

Best Answer

  • J Tech
    J Tech ✭✭✭✭✭
    Answer ✓
    Options

    Hi @Dami

    You have the right idea with using the IF function, but the syntax is a little off. Try using the following formula:

    =IF([Risk Category]@row="High", TODAY()+15, IF([Risk Category]@row="Medium", TODAY()+45, IF([Risk Category]@row="Low", TODAY()+60, "")))

    In this formula, the IF function checks the value in the Risk Category column for each row. If the value is "High", it returns the current date plus 15 days. If the value is "Medium", it returns the current date plus 45 days. If the value is "Low", it returns the current date plus 60 days. If the Risk Category value is none of those, it returns a blank cell.

    Regards

    J Tech

    If my response has helped you in any way or provided a solution to your problem, please consider supporting the community by marking it as Insightful, Vote Up, or Awesome. Additionally, you can mark it as the accepted answer, which will make it easier for others to find a solution or get help with a similar issue in the future. Your support is greatly appreciated!

Answers

  • J Tech
    J Tech ✭✭✭✭✭
    Answer ✓
    Options

    Hi @Dami

    You have the right idea with using the IF function, but the syntax is a little off. Try using the following formula:

    =IF([Risk Category]@row="High", TODAY()+15, IF([Risk Category]@row="Medium", TODAY()+45, IF([Risk Category]@row="Low", TODAY()+60, "")))

    In this formula, the IF function checks the value in the Risk Category column for each row. If the value is "High", it returns the current date plus 15 days. If the value is "Medium", it returns the current date plus 45 days. If the value is "Low", it returns the current date plus 60 days. If the Risk Category value is none of those, it returns a blank cell.

    Regards

    J Tech

    If my response has helped you in any way or provided a solution to your problem, please consider supporting the community by marking it as Insightful, Vote Up, or Awesome. Additionally, you can mark it as the accepted answer, which will make it easier for others to find a solution or get help with a similar issue in the future. Your support is greatly appreciated!
  • Dami
    Dami ✭✭
    Options

    Perfect. That worked! Thank you so much.