Invalid Operation
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!
Best Answer
-
You would wrap it in an IFERROR statement.
=IFERROR(original_formula, "")
Answers
-
You would wrap it in an IFERROR statement.
=IFERROR(original_formula, "")
-
Yup that worked thanks so much!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!