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.
Best Answer
-
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.
Answers
-
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.
-
Thanks so. much for helping me see the simple way of doing this. GREATLY Appreciated!!
-
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)))
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.5K Get Help
- 432 Global Discussions
- 152 Industry Talk
- 494 Announcements
- 5.3K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 76 Community Job Board
- 504 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 307 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!