Help with IF function combining multiple columns and greater than today dates

Hello I am trying to write a formula that returns one of the following "Not Yet Started, "In Progress," or "Complete" based on dates being in the past or future and multiple columns.
So I would like the Study Status Prod column to say "Not Yet Started" if the date in the Anticipated Study Start Date column is before today and the Study Status Prod column to say "In Progress" if the date in the Initiation of Study Date column is before today and the Study Status Prod Column to say "Complete" if the Final Report (Target Date) column has a date after today.
Is there a way to write this formula?
Thank you in advance!
Best Answer
-
Hello @RPlaud
The formula handbook is a great reference.
I made some assumptions on your process flow. Will the formula below work for you?
=IF(AND(ISDATE([Final Report (Target Date)]@row), [Final Report (Target Date)]@row <= TODAY()), "Complete", IF(AND(ISDATE([Initiation of Study Date]@row), [Initiation of Study Date]@row <= TODAY()), "In Progress", IF(AND(ISDATE([Anticipated Study Start Date]@row), [Anticipated Study Start Date]@row <= TODAY()), "Not Yet Started")))
Kelly
Answers
-
I am not the best with formulas, but Smartsheet has helped with the Formula Handbook Template. Check it out, may help with your question.
If this comment helped you, please help me and help others by using the buttons below if you found it💡Insightfulor❤️Awesome!
Hope you have a great day!!
Ed
-
Hello @RPlaud
The formula handbook is a great reference.
I made some assumptions on your process flow. Will the formula below work for you?
=IF(AND(ISDATE([Final Report (Target Date)]@row), [Final Report (Target Date)]@row <= TODAY()), "Complete", IF(AND(ISDATE([Initiation of Study Date]@row), [Initiation of Study Date]@row <= TODAY()), "In Progress", IF(AND(ISDATE([Anticipated Study Start Date]@row), [Anticipated Study Start Date]@row <= TODAY()), "Not Yet Started")))
Kelly
-
I cannot thank you enough! This worked perfectly!!!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.1K Get Help
- 430 Global Discussions
- 149 Industry Talk
- 489 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 154 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!