At Risk Formula Help

Question

Hello!

I am pretty new and looking for help with a formula. The project I am working on has a 5 business day expected turn around time. I want to create flags to visually show if a project is on track, close to due or due/overdue.

Basically:

if "Request Due" By is 3-5 days from "Created Date" and status is at "Sent to Accounts Receivable" = Green Dot in "Health" column

if "Request Due" By is 1-2 days from "Created Date" and status is at "Sent to Accounts Receivable" = Yellow Dot in "Health" column

if "Request Due" By is 0/Past Due days from "Created Date" and status is at "Sent to Accounts Receivable" = Red Dot in "Health" column

*data shown is dummy data*

Kelly Moore · Accepted Answer

Hey @ldavenportHAP

Try this:

=IF(AND([Request Due By]@row >= [Created Date]@row + 3, Status@row = "Sent to Accounts Receivable"), "Green", IF(AND([Request Due By]@row >= [Created Date]@row + 1, Status@row = "Sent to Accounts Receivable"), "Yellow", IF(AND([Request Due By]@row <= [Created Date]@row, Status@row = "Sent to Accounts Receivable"), "Red")))

You may find more conditions that need to be added - like what happens if Status isn't "sent to Accounts Receivable'.  Right now this brings a blank cell. Let me know if the formula needs more tweaking.

Kelly

Kelly Moore · Accepted Answer

Perfect! I'm glad you figured it out!  Consider adding a statement as the very first IF of what makes a Request = "Complete". As it is written now, eventually all of your requests will turn red as there is nothing to stop the clock.  If you need help inserting that, let me know.

Kelly