IF Formula

malky neiman

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.

Kaveri Vipat

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!

Ipshita

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

Kaveri Vipat

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!

malky neiman

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