Return a value based on looking at two cells

Carl Vieira

Hello SS Community,

I am having a bit of formula writing block. I am trying to return a value based on looking at two cells. For example in the picture below.

If Onboarding Complete Date is blank and I9 Complete Date is blank, change the OB Status column to 'Awaiting Talent Paperwork'

If Onboarding Completed Date is populated and the I9 completion date is blank, change the OB status column to 'Awaiting I9 Section 2'.

If Onboarding Completed Date is populated and the I9 completion date is populated, change the OB status column to "Complete'.

Any assistance would be greatly appreciated.

Andrée Starå

Hi @Carl Vieira

Try something like this.

=IF(AND([Onboarding Complete Date]@row = ""; [I9 Complete Date]@row = ""); "Awaiting Talent Paperwork"; IF(AND([Onboarding Complete Date]@row <> ""; [I9 Complete Date]@row = ""); "Awaiting I9 Section 2"; IF(AND([Onboarding Complete Date]@row <> ""; [I9 Complete Date]@row <> ""); "Complete")))

The same version but with the below changes for convenience.

=IF(AND([Onboarding Complete Date]@row = "", [I9 Complete Date]@row = ""), "Awaiting Talent Paperwork", IF(AND([Onboarding Complete Date]@row <> "", [I9 Complete Date]@row = ""), "Awaiting I9 Section 2", IF(AND([Onboarding Complete Date]@row <> "", [I9 Complete Date]@row <> ""), "Complete")))

Depending on your country/region, you'll need to exchange the comma to a period and the semi-colon to a comma.

Did that work?

I hope that helps!

Be safe and have a fantastic day!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Andrée Starå

Hi @Carl Vieira

Try something like this.

=IF(AND([Onboarding Complete Date]@row = ""; [I9 Complete Date]@row = ""); "Awaiting Talent Paperwork"; IF(AND([Onboarding Complete Date]@row <> ""; [I9 Complete Date]@row = ""); "Awaiting I9 Section 2"; IF(AND([Onboarding Complete Date]@row <> ""; [I9 Complete Date]@row <> ""); "Complete")))

The same version but with the below changes for convenience.

=IF(AND([Onboarding Complete Date]@row = "", [I9 Complete Date]@row = ""), "Awaiting Talent Paperwork", IF(AND([Onboarding Complete Date]@row <> "", [I9 Complete Date]@row = ""), "Awaiting I9 Section 2", IF(AND([Onboarding Complete Date]@row <> "", [I9 Complete Date]@row <> ""), "Complete")))

Depending on your country/region, you'll need to exchange the comma to a period and the semi-colon to a comma.

Did that work?

I hope that helps!

Be safe and have a fantastic day!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Carl Vieira

@Andrée Starå it works beautifully! I really appreciate your assistance.

Regards,

Carl

Andrée Starå

@Carl Vieira

Excellent!

You're more than welcome!