Is there a rule to change Priority in a Column, when Due Date in another column is within 30 days

ToddRAus

I have a sheet set up with a list of tasks

One column is dedicated for "Priority" *High, Medium, Low" Importance.

Once column has due dates, for each of the line item tasks

Is there a rule or formula that can be crated where the Priority can change in the individual cell of the line item, when the associated Due Date, in the Due date column, is within 30, 60 or 90 days?

This would help me see what has fallen within 30 days and needs immediate attention, especially when the list is incredibly long.

Thank you in advance!!!

KPH

You can use an IF formula in the priority column to set the level (low, medium, high) based on whatever logic you desire.

The logic could include a TODAY function to evaluate the proximity of the Due Date to the current date.

IF formula for priorities

To create three different priorities you would need to nest two IF functions. If placed in the value-if-false location within the first IF, the second IF will be evaluated only if the first is false. The syntax would look like this:

=IF(first logic , value if true ,IF(second logic , value if true , value if false))

In your case, the logic could be:

• If difference between Today and Due date is less than 30 days = high
• ---If that isn't true but difference between Today and Due date is less than 60 days = medium
• ------If that isn't true then = low

(or if you want the low to be anything due within 90 days and anything more than 90 days to not be flagged at all you can add another IF to the nest).

TODAY Function for due dates

This function means the date in the [Due date] column (which must be formatted as a Date column type) is less than (i.e. before) the date Today plus 30 days. In other words the due date is within the next 30 days.

[Due date]@row < TODAY(30)

Final Formula

The final formula would look something like this:

=IF([Due date]@row < TODAY(30), "High", IF([Due date]@row < TODAY(60), "Medium", "Low"))

And that would create priorities like this (with today being February 20th 2024).

Hope this helps!