IF Formula
Hi everyone. Im stuck with a formula and need help.
this is a screensot of the sheet im working on. I have a formula in the APP STATUS column. it basically returns the status to show if all items of an application is processed (complete). here is the working formula i have:
=IF(AND([Profile Picture]@row = "Complete", References@row = "Complete", [I9]@row = "Complete", [Other Items]@row = "Complete", [Proof of residency]@row = "Complete", [State Background Check]@row = "Complete", [TB Test]@row = "Complete"), "Ready for Orientation", "Application in Process")
I want to add another step to the formula. If ORIENTATION DATE is today or in the future and all cells (from the existing formula) are marked as complete, it should return a new "Scheduled for orientation" Formula.
Best Answer
-
Please use the following formula for the required conditions-
=IF(AND([Profile Picture]@row = "Complete", References@row = "Complete", [I9]@row = "Complete", [Other Items]@row = "Complete", [Proof of residency]@row = "Complete", [State Background Check]@row = "Complete", [TB Test]@row = "Complete", [Orientation Date]@row >= TODAY()), "Scheduled for orientation", IF(AND([Profile Picture]@row = "Complete", References@row = "Complete", [I9]@row = "Complete", [Other Items]@row = "Complete", [Proof of residency]@row = "Complete", [State Background Check]@row = "Complete", [TB Test]@row = "Complete"), "Ready for Orientation", "Application in Process"))
I hope this helps!
Thanks,
Kaveri Vipat
Senior Associate - Smartsheet Development, Ignatiuz Software
2023 Core Product Certified
Did this answer help you? Show some love by marking this answer as "Insightful💡" or "Awesome❤️" and "Vote Up⬆️"
Answers
-
Hello @malky neiman You need to add another IF statement to include the orientation date criteria to your existing formula so that if first logic is not true then the second IF statement will run and test for the logic.
Hope this helps,
Cheers,
Ipshita
Ipshita Mukherjee
-
Please use the following formula for the required conditions-
=IF(AND([Profile Picture]@row = "Complete", References@row = "Complete", [I9]@row = "Complete", [Other Items]@row = "Complete", [Proof of residency]@row = "Complete", [State Background Check]@row = "Complete", [TB Test]@row = "Complete", [Orientation Date]@row >= TODAY()), "Scheduled for orientation", IF(AND([Profile Picture]@row = "Complete", References@row = "Complete", [I9]@row = "Complete", [Other Items]@row = "Complete", [Proof of residency]@row = "Complete", [State Background Check]@row = "Complete", [TB Test]@row = "Complete"), "Ready for Orientation", "Application in Process"))
I hope this helps!
Thanks,
Kaveri Vipat
Senior Associate - Smartsheet Development, Ignatiuz Software
2023 Core Product Certified
Did this answer help you? Show some love by marking this answer as "Insightful💡" or "Awesome❤️" and "Vote Up⬆️"
-
the formula worked! Can u help me take it 1 step further? i now added another column named HIRED.
i want to the new formula that if the hired date has any date (doesnt matter if its in the past or future), it should return a status of "Hired"
thank you for all ur help
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!