Priority Column Change based on Column Date

Options
edited 07/12/24

Hello, I am trying to use an IF formula to change a Priority Column based on a Service Start Date.

If the Service Date is within 30 days, Priority column would change to High.

If the Service Date is within 40 days, Priority column would be Medium.

If the Service Date is over 50 days, Priority column would be Low.

The Priority Column is set up as a dropdown list, "Low, Medium, High."

Service Date Column is set up as Date.

Here is the formula I was trying to use:

=IF(AND([Service Date]@row, [Priority]@row) < [Due Date] (30), "High", IF(AND([Service Date]@row, [Priority]@row > [Due Date] (40), "Medium", IF(AND([Service Date]@row, [Priority]@row) > [Due Date] (50)"Low"))

Thank you!

Mandy

• ✭✭✭✭✭
edited 07/12/24
Options

You don't need to include Priority in your formula, because Priority is essentially your output column… where it feels like this formula would BE. One thing I considered is what the priority should be if the service date is between 40 and 50 days - according to your description, there isn't any priority to those. I'm making the assumption that anything that isn't High or Medium priority is Low priority - which will accommodate for those above 40 days.

=IF([Service Date]@row <= TODAY(30), "High", IF([Service Date]@row <= TODAY(40), "Medium", "Low"))

Good luck!

• ✭✭✭✭✭
Options
```=IF([Service Start Date]@row < TODAY(30), "High",
IF([Service Start Date]@row < TODAY(40), "Medium",
IF([Service Start Date]@row < TODAY(50), "Low", "")))
```

...

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!