Is there a way around an Invalid Operation when using Dates generated by formulas
Hi,
I am trying to use this formula =IF([Next inspection due]@row <= TODAY(0), 1, 0) then mark it with a flag. in the Flag Due Date column.
Every time I use this formula I get INVALID OPERATION because the Next inspection due column is created from a formula of a formula of a date.
Date6 is add on 6 months, Date1 is add on 1 year, and Date2 is add on 2 years. Next inspection date then checks if they got an A, B, or a C. This will then determine when their next inspection is due.
Formulas used:
Date6 - =IFERROR(DATE(YEAR([Date of last Inspection]@row), MONTH([Date of last Inspection]@row) + 6, DAY([Date of last Inspection]@row)), DATE(YEAR([Date of last Inspection]@row) + 1, MONTH([Date of last Inspection]@row) - 6, DAY([Date of last Inspection]@row)))
Date1 - =IFERROR(DATE(YEAR([Date of last Inspection]@row) + 1, MONTH([Date of last Inspection]@row), DAY([Date of last Inspection]@row)), DATE(YEAR([Date of last Inspection]@row), MONTH([Date of last Inspection]@row), DAY([Date of last Inspection]@row)))
Date2 - =IFERROR(DATE(YEAR([Date of last Inspection]@row) + 2, MONTH([Date of last Inspection]@row), DAY([Date of last Inspection]@row)), DATE(YEAR([Date of last Inspection]@row), MONTH([Date of last Inspection]@row), DAY([Date of last Inspection]@row)))
Next inspection due - =IF(HAS(Grade@row, "A"), JOIN([Date6]@row), IF(HAS(Grade@row, "B"), JOIN([Date1]@row), IF(HAS(Grade@row, "C"), JOIN([Date2]@row), "")))
Flag Due Date - =IF([Next inspection due]@row <= TODAY(0), 1, 0)
This last one gives me the Invalid Operation.
I have tried JOIN but that results in the same problem. I have tried on another sheet just replicating the last step with a JOIN and it will work then. It seems to be with all of the formulas together it will not work.
Thank you for reading this and I would greatly appreciate any help.
Answers
-
You might've said this so sorry if you did but, is the Next Inspection Due column a date column type or a text column type?
I see the date in the column but the column type typically gives this error. You can have a date column type with a formula in it.
-
Hi Dakota. Thank you for the response. It is indeed a Date column. Any column that should show a date is a date column with a formula in it.
-
Hi @Simon Bamford,
Try inserting an If statement at the beginning of the Date6, Date1 and Date2 formulas as per the below and see if that helps.
Date6 - =IF([Date of last Inspection]@row="","",IFERROR(DATE(YEAR([Date of last Inspection]@row), MONTH([Date of last Inspection]@row) + 6, DAY([Date of last Inspection]@row)), DATE(YEAR([Date of last Inspection]@row) + 1, MONTH([Date of last Inspection]@row) - 6, DAY([Date of last Inspection]@row))))
Then amend the Next Inspection Due as follows.
Next inspection due - =IF([Date of last Inspection]@row="","",IF(HAS(Grade@row, "A"), JOIN([Date6]@row), IF(HAS(Grade@row, "B"), JOIN([Date1]@row), IF(HAS(Grade@row, "C"), JOIN([Date2]@row), ""))))
Hope this helps!
John
-
Thank you John but no luck unfortunately. I think I had a brainwave though.
I have created a new column called 1 and set up automation that when an inspection date is reached it will put a 1 in the cell. Then a formula goes into the Flag Due Date column that says if column 1 has a 1 in it then flag it.
Just testing this now but I completely forgot about automation as an answer here.
Thank you John and Dakota for the help.
-
Try getting rid of the JOIN functions. The JOIN function will output a text string regardless of what it is pulling and what type of column it is in. Just use a straight cell reference instead.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Thanks Paul. That is good advice and something I need to make sure I do going forward.
-
Did that solve the issue, or are you still getting an error message?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Oh I still got the error unfortunately but with the automation I have got the solution to work instead.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!