Invalid Operation


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


Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!