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*
Best Answers
-
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
-
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
Answers
-
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
-
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"!
-
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
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!