Invalid Value Error on Dates (even when the column types are all DATE fields)

So I have a field that is looking up a date value from another sheet. (Index/Match)

I am trying to get a date that is 6 months prior to that date in a different field.

I am using a simple "if the affiliation date is blank, return blank"

My formula is below.

=IF(ISBLANK([Affiliation Date]@row), "", DATE(YEAR([Affiliation Date]@row), MONTH([Affiliation Date]@row) - 6, DAY([Affiliation Date]@row)))

I keep getting a "INVALID VALUE" error on the majority of my cells (but not all cells).

Any help would be greatly appreicated.

Find more posts tagged with

Accepted answers

Jgorsich

Anything in January or May would give you a negative month value, which would be invalid. You can either just subtract 180 days from the date in question when not blank (roughly 6 months) OR you can do some fancy if() work to determine what to do when you are outside of the acceptable range. If you opt for just subtracting 180 you can skip the work with the date() function entirely and just go "[affiliation date]@row-180".

I haven't tested it, but the following is what the fancy if() work likely looks like:

=IF(ISBLANK([Affiliation Date]@row), "", DATE(if(MONTH([Affiliation Date]@row)<7,YEAR([Affiliation Date]@row-1,YEAR([Affiliation Date]@row)), if(MONTH([Affiliation Date]@row)<7,MONTH([Affiliation Date]@row)+6,MONTH([Affiliation Date]@row) - 6), DAY([Affiliation Date]@row)))

basically, if the month is June or earlier, add 6 instead of subtracting 6 (or, subtract 6 and then add 12, so Feb (2) become -4+12= August(8), and subtract 1 from the year.

All comments

Jgorsich

I haven't tested it, but the following is what the fancy if() work likely looks like:

basically, if the month is June or earlier, add 6 instead of subtracting 6 (or, subtract 6 and then add 12, so Feb (2) become -4+12= August(8), and subtract 1 from the year.

Christopher.Rose

Thanks so. much for helping me see the simple way of doing this. GREATLY Appreciated!!

Paul Newcome

Here is another way of doing it that may be a little easier to modify.

=IF([Affiliation Date]@row <> "", IFERROR(DATE(YEAR([Affiliation Date]@row), MONTH([Affiliation Date]@row) - 6, DAY([Affiliation Date]@row)), DATE(YEAR([Affiliation Date]@row) - 1, MONTH([Affiliation Date]@row) + 6, DAY([Affiliation Date]@row)))

As long as the absolute values of the -6 and +6 equal 12, you can easily modify the formula to use 4 months, 8 months, or any other number of months (less than 12 of course). Going back 4 months would be -4 and +8. Going back 2 months would be -2 and +10, so on and so forth.

If you wanted to use IF statements instead, I would suggest this:

=IF([Affiliation Date]@row <> "", DATE(YEAR([Affiliation Date]@row) - IF(MONTH([Affiliation Date]@row) <= 6, 1, 0), MONTH([Affiliation Date]@row) + IF(MONTH([Affiliation Date]@row) >= 6 , -6, 6), DAY([Affiliation Date]@row)))

Jgorsich

For anyone who reads this in the future - Paul's use of the iferror statement is a LOT more elegant and absolutely a cleaner way to go about it. In general, I've found 2 things to be true:

I've NEVER failed to make a formula better by adding "iferror" into it SOMEWHERE
Paul Newcome's answers are the best around.

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.