IF Formula

Options

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.

Tags:

Best Answer

  • Kaveri Vipat
    Kaveri Vipat ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi malky neiman, 

    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

  • Ipshita
    Ipshita ✭✭✭✭✭✭
    Options

    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

  • Kaveri Vipat
    Kaveri Vipat ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi malky neiman, 

    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⬆️"

  • malky neiman
    Options

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!