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!

Jo Vargas

Hi, I'd like to use this formula, but I'll need the logic to be:

IF Due Date is within the next 30 days or in the past, priority should be HIGH

IF Due Date is in the past, but the status is 'complete', I want the priority column to be blank.

Any tips?

Thanks,

Jo

KPH

Hi @Jo Vargas

You can add an additional nested IF to return blank if the Due Date is in the past and the Status is also complete. As these are evaluated in order, this is placed at the front of the formula to be evaluated before considering if the Due Date is in the next 30 days. The rest of the formula remains the same

• IF Due Date is within the next 30 days or in the past, priority should be HIGH
• IF Due Date is not within the next 30 days or in the past, but is within the next 60 days, priority should be MEDIUM
• Otherwise, Priority is LOW

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

The output would look this like:

If you would like the Priority to be blank whenever the Status is complete (rather than just when the due date is soon/past). You can remove the AND part and use this formula:

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

These formula will always return High if there is no Due date. You can add another IF to remove this, if you would like.

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

Jo Vargas

Thanks for your response. These work!!

KPH

😉 Great to hear