What formula do i use for date ranges = Priority?

CAS

I have a priority column

I have a invoice date column

I require a formula that changes the priority:

to high for anything over 90 days old of the invoice date

To medium for anything between 61 - 89 days old of the invoice date

To low for anything 0 to 60 days old from the invoice date

can anyone help with a formula and/or is there a automation that can be setup?

kind regards

C

Nick Korna

Hi @CAS,

You can do this with a formula in the priority column:

=IF(TODAY() - [Invoice Date]@row >= 90, "High", IF(TODAY() - [Invoice Date]@row >= 61, "Medium", "Low"))

Hope this helps, but if you've any questions or comments then just post! 😊

CAS

Thank Nick,

that works for High and Medium but the low is anything from 0 to 60 days.

I've tried amending the formula but it doesn't work for Low

=IF(TODAY() - [Invoice Date]@row >= 90, "High", IF(TODAY() - [Invoice Date]@row >= 61, "Medium", IF(TODAY() - [Invoice Date]@row >= 60, "Low")))

Nick Korna

If you want it to exclude any blanks or future dated invoices:

=IF(TODAY() - [Invoice Date]@row >= 90, "High", IF(TODAY() - [Invoice Date]@row >= 61, "Medium", IF(TODAY() - [Invoice Date]@row >= 0, "Low", "")))

Example:

The previous formula was separating out the High & Mediums and anything else would be Low, since if it's 90+ days it will be caught by the IF..."High" and anything 61-89 by the "Medium".

Your formula would only give a result of "Low" for anything on exactly 60 days.

Let me know if I've misunderstood anything or there are any further changes needed! 😊