Creating At-Risk Formula for Level of Priorities

Natalia Pagano

Hello,

I'm not sure where to begin for this so looking for some guidance.

In our Legal Management Sheet we prioritize requests by the following:

Urgent - Less than 1-3 days turnaround time to execute the contract. The vendor contract is vital and needed immediately to complete an initiative/operation of the business (excl. price discounts). Critical impact,work cannot continue

High - There is a 3-7 days turnaround time to execute the contract. The vendor contract is needed to complete an initiative/operation of the business (excl. price discounts). Significant Impact

Medium - There is a 7-14 days turnaround time to execute the contract. The vendor contract is necessary but not immediately needed to complete an initiative/operation of the business. Moderate impact

Low - There are 14+ days to execute the contract. The vendor contract could be for a future initiative/operation of the business. Limited Impact

I would like to create an at-risk column or something similar that would flag the request if it's reaching towards the max end of the priority. For example, if the request (submitted by a form) is a medium priority, I would like the column to flag that there's only x days left before we pass the turnaround time of 14 days.

Essentially, a way to track and calculate turnaround time.

Thank you!

David Tutwiler

If you are looking to flag an At Risk flag or turn a Health bubble a different color, you could do something like this. It is a series of Nested IF statements that would flag rows as At Risk based on the Priority and TODAY() vs the projected End Date.

For this example I have written the formula to look for Urgent tasks that are 1 day away from needing to be completed and High tasks within 3 days of needing to be completed. You could modify the date fields and add to this for your other Priorities as needed. This formula also assumed that you are marking a flag to make the row At Risk and that this formula is in the At Risk column.

=IF(AND(Priority@row = "Urgent", [End Date]@row - TODAY() < 2), true, IF(AND(Priority@row = "High", [End Date]@row - TODAY() < 4), true))