At Risk Formula Help

ldavenportHAP

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

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

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

Kelly Moore

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

ldavenportHAP

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!

ldavenportHAP

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"!

Kelly Moore

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