Is there a way around an Invalid Operation when using Dates generated by formulas

Simon Bamford

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.

Dakota Haeffner

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.

Simon Bamford

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.

John_Foster

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

Simon Bamford

https://community.smartsheet.com/discussion/comment/424438#Comment_424438

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.

Paul Newcome

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.

Simon Bamford

Thanks Paul. That is good advice and something I need to make sure I do going forward.

Paul Newcome

Did that solve the issue, or are you still getting an error message?

Simon Bamford

https://community.smartsheet.com/discussion/comment/424776#Comment_424776

Oh I still got the error unfortunately but with the automation I have got the solution to work instead.