Invalid Operation

BB2791

Hello,

I have a sheet tracking inspection dates. I have a status column with a formula that assigns a status based on when the expiration date is in relation to the current date. The status column is populating properly when there is an expiration date value in the "expiration date" column. However, for rows that have a blank expiration date the status column is showing "Invalid Operation." I think this is because I made the status column a formula column and I imagine the error is due to the fact the system can't get the data it needs to assign a status. If that is correct, is there a way to override that so those status cells remain blank until someone fills in the missing values? I don't want to share the sheet with an error in it.

I've attached a screenshot. The formulas I'm using for the Exp Date and Status columns are below for reference:

Expiration Date: =IF([Inspection Cycle]@row = "Once", "No expiration", IF([Inspection Cycle]@row = "Annually", DATE(YEAR([Last Inspection Date]@row) + 1, MONTH([Last Inspection Date]@row), DAY([Last Inspection Date]@row)), IF([Inspection Cycle]@row = "Bi-annually", DATE(YEAR([Last Inspection Date]@row), MONTH([Last Inspection Date]@row) + 6, DAY([Last Inspection Date]@row)), "")))

Status: =IF([Expiration Date]@row = "No expiration", "No date", IF([Expiration Date]@row < TODAY(), "Expired", IF([Expiration Date]@row - TODAY() <= 90, "Notifying", "Current")))

Thanks!

Paul Newcome

You would wrap it in an IFERROR statement.

=IFERROR(original_formula, "")

Paul Newcome

You would wrap it in an IFERROR statement.

=IFERROR(original_formula, "")

BB2791

Yup that worked thanks so much!

Paul Newcome

Happy to help. 👍️