# At Risk Formula Help

✭✭✭✭

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*

• ✭✭✭✭✭✭

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

• ✭✭✭✭✭✭

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

• ✭✭✭✭✭✭

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

• ✭✭✭✭
edited 04/03/22

Thank you so much@Kelly Moore ! That worked!

hmm.. I messed up my thought process though since neither the Created Date nor Request Due by Date change.. I dont think the Health symbol will change. I think maybe i need a column with todays date and to play around with the formula a bit? Back to the drawing board!

• ✭✭✭✭

Oh i think i figured it out! i just added a "todays date" column and changed the "created date" in the formula to "todays date"!

• ✭✭✭✭✭✭