What formula to use to show how status changes depending on days prior to due date
Best Answers
-
Hi @Profe17
Hope you are fine, please try the following formula and convert it to a column format formula:
=IFERROR(IF(TODAY() - [due date]@row < 15, "Red", IF(TODAY() - [due date]@row > 20, "Green", "Yellow")), "")
the following screenshots shows the result:
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
Hi @Profe17
Hope you are fine, Please try the following formula:
=IF(AND(TODAY() - [due date]@row < 20, TODAY() - [due date]@row > 15, Status@row = "not started"), "Yellow", IF(AND(TODAY() - [due date]@row < 15, Status@row = "not started"), "Red", IF(AND(TODAY() - [due date]@row > 20, Status@row = "not started"), "Green", "")))
- and for your question " How can I add in absolute value to the above formula?" use the function ABS Function
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
Answers
-
Hi @Profe17
Hope you are fine, please try the following formula and convert it to a column format formula:
=IFERROR(IF(TODAY() - [due date]@row < 15, "Red", IF(TODAY() - [due date]@row > 20, "Green", "Yellow")), "")
the following screenshots shows the result:
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
Thank you! That worked!
-
This formula worked, but I now need to bring in more information and i'm wondering if that is possible.
=IFERROR(IF(TODAY() - [due date]@row < 15, "Red", IF(TODAY() - [due date]@row > 20, "Green", "Yellow")), "")
My question is two parts.
- How can I add in absolute value to the above formula?
- Can I add in another variable that would include status? Please see below
- Today is 15-19 days prior to due date and status = "not started" so health is "Yellow"
- Today is < 15 days prior to due date and status = "not started" so health is "red"
- Today is 20+ days prior to due date and status = "not started" so health is "green"
-
Hi @Profe17
Hope you are fine, Please try the following formula:
=IF(AND(TODAY() - [due date]@row < 20, TODAY() - [due date]@row > 15, Status@row = "not started"), "Yellow", IF(AND(TODAY() - [due date]@row < 15, Status@row = "not started"), "Red", IF(AND(TODAY() - [due date]@row > 20, Status@row = "not started"), "Green", "")))
- and for your question " How can I add in absolute value to the above formula?" use the function ABS Function
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
That worked perfectly! Thanks again!
-
You are welcome and I will be happy to help you any time.
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!