IF AND with Dates
Help! I've searched and I am stuck! I'm new to SS and I cannot get any formula to work. Here's what I'm trying to do;
If the status is "In Process" and today's date is greater than 7 days from the due date = Green
If the status is "In Process" and today's date is within 7 days of the due date = yellow
If the status is "In Process" and today's date is equal to or after the due date = red
If the status is 'Complete' = blue
Best Answers
-
Hi @JmeC
Hope you are fine, please try the following formula and convert it to column format formula:
=IFERROR(IF(OR([due date]@row = "", status@row = ""), "", IF(AND(status@row = "In Progress", [due date]@row >= TODAY(7)), "Green", IF(AND(status@row = "In Progress", [due date]@row < TODAY(7), [due date]@row > TODAY()), "Yellow", IF(AND(status@row = "In Progress", [due date]@row <= TODAY(7)), "Red")))), "")
the following screenshot 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"
-
@JmeC
The following formula will turn it blue if Status = Complete
=IFERROR(IF(status@row = "Complete", "Blue", IF(OR([due date]@row = "", status@row = ""), "", IF(AND(status@row = "In Progress", [due date]@row >= TODAY(7)), "Green", IF(AND(status@row = "In Progress", [due date]@row < TODAY(7), [due date]@row > TODAY()), "Yellow", IF(AND(status@row = "In Progress", [due date]@row <= TODAY(7)), "Red"))))), "")
the following screenshot 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"
Answers
-
Hi @JmeC
Hope you are fine, please try the following formula and convert it to column format formula:
=IFERROR(IF(OR([due date]@row = "", status@row = ""), "", IF(AND(status@row = "In Progress", [due date]@row >= TODAY(7)), "Green", IF(AND(status@row = "In Progress", [due date]@row < TODAY(7), [due date]@row > TODAY()), "Yellow", IF(AND(status@row = "In Progress", [due date]@row <= TODAY(7)), "Red")))), "")
the following screenshot 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"
-
OMG thank you @Bassam Khalil ! I spent HOURS trying to get it to work and you SAVED me and my sanity!! Sending you a giant virtual hug!!
-
@JmeC
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"
-
@Bassam Khalil sorry one more: where do I add the command to turn it blue if Staus = Complete, i tried at the end and it isn't working.
If the status is 'Complete' = blue
=IFERROR(IF(OR([due date]@row = "", status@row = ""), "", IF(AND(status@row = "In Progress", [due date]@row >= TODAY(7)), "Green", IF(AND(status@row = "In Progress", [due date]@row < TODAY(7), [due date]@row > TODAY()), "Yellow", IF(AND(status@row = "In Progress", [due date]@row <= TODAY(7)), "Red")))), "")
IF([status]@row = "Complete", "blue"
-
I will fix it for you tomorrow because I left my office now.
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"
-
@JmeC
The following formula will turn it blue if Status = Complete
=IFERROR(IF(status@row = "Complete", "Blue", IF(OR([due date]@row = "", status@row = ""), "", IF(AND(status@row = "In Progress", [due date]@row >= TODAY(7)), "Green", IF(AND(status@row = "In Progress", [due date]@row < TODAY(7), [due date]@row > TODAY()), "Yellow", IF(AND(status@row = "In Progress", [due date]@row <= TODAY(7)), "Red"))))), "")
the following screenshot 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"
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 462 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives