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")