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.

Find more posts tagged with

Sheets

Formulas

Accepted answers

All comments

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.