Nested IF formula resulting in #INVALID DATA TYPE

Natalia Kataoka

I am working on a formula to indicate whether a vendor is approved for work and needed to add another condition and a new status. It is working for the most part, but a few rows are pulling #invaliddatatype and I cannot see how to fix it.

What I want the formula to say: If the [Agreement Expiration Date] was last year and the [Ins Status] is Current or N/A = "YES - Update Pending", if the [Agreement Type] is MBPO, Truck, or BPO and the [Ins Status] is Current or N/A ="YES", if the [Ins Status] is Current or N/A and [Agreement EXE] is checked = "YES", if [Ins Status] is Contact Natalia = "Contact Natalia", otherwise "No".

I have gotten the below formula to work 95% of the time, but it seems to pull the #invalid data type error for any row that has a blank in Agreement Expiration Date.

=IF(AND([Ins Status]@row = "Current", YEAR([Agreement Expiration Date]@row) = YEAR(TODAY()) - 1), "YES - Update Pending", IF(AND(OR([Agreement Type]@row = "MBPO", [Agreement Type]@row = "TRUCK", [Agreement Type]@row = "BPO"), OR([Ins Status]@row = "Current", [Ins Status]@row = "N/A"), [Agreement EXE]@row = 1), "YES", IF(AND(OR([Ins Status]@row = "Current", [Ins Status]@row = "N/A"), [Agreement EXE]@row = 1), "YES", IF([Ins Status]@row = "Contact Natalia prior to Scheduling", "CONTACT NATALIA PRIOR TO SCHEDULING", "NO"))))

Basically what I need in those rows with errors is any that have Ins Status of Contact Natalia, to have App'd for Work also = Contact Natalia, and those with other Ins Status = NO

Jeff Reisman

Try wrapping the whole thing in an IFERROR to set the value to "NO" if there's an error, since any row with a blank date value will be unable to produce a valid value in YEAR([Agreement Expiration Date]@row). See additions to your formula in BOLD.

=IFERROR(IF(AND([Ins Status]@row = "Current", YEAR([Agreement Expiration Date]@row) = YEAR(TODAY()) - 1), "YES - Update Pending", IF(AND(OR([Agreement Type]@row = "MBPO", [Agreement Type]@row = "TRUCK", [Agreement Type]@row = "BPO"), OR([Ins Status]@row = "Current", [Ins Status]@row = "N/A"), [Agreement EXE]@row = 1), "YES", IF(AND(OR([Ins Status]@row = "Current", [Ins Status]@row = "N/A"), [Agreement EXE]@row = 1), "YES", IF([Ins Status]@row = "Contact Natalia prior to Scheduling", "CONTACT NATALIA PRIOR TO SCHEDULING", "NO")))), "NO")

Jeff Reisman

Try wrapping the whole thing in an IFERROR to set the value to "NO" if there's an error, since any row with a blank date value will be unable to produce a valid value in YEAR([Agreement Expiration Date]@row). See additions to your formula in BOLD.

=IFERROR(IF(AND([Ins Status]@row = "Current", YEAR([Agreement Expiration Date]@row) = YEAR(TODAY()) - 1), "YES - Update Pending", IF(AND(OR([Agreement Type]@row = "MBPO", [Agreement Type]@row = "TRUCK", [Agreement Type]@row = "BPO"), OR([Ins Status]@row = "Current", [Ins Status]@row = "N/A"), [Agreement EXE]@row = 1), "YES", IF(AND(OR([Ins Status]@row = "Current", [Ins Status]@row = "N/A"), [Agreement EXE]@row = 1), "YES", IF([Ins Status]@row = "Contact Natalia prior to Scheduling", "CONTACT NATALIA PRIOR TO SCHEDULING", "NO")))), "NO")

Natalia Kataoka

Perfect, thanks!