Formulas and Functions

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

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

  • ✭✭✭✭✭
    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

  • ✭✭✭✭✭
    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.

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

  • Community Champion

    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:

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions

  • I have a pretty basic sheet that I am using to develop a formula, and am encountering an issue I haven't seen before. The formula isn't including my second row for some reason. It isn't a huge issue f…
    User: "jjg279"
    Answered ✓
    9
    2
  • I'm sure that this is an easy fix but I've tried a bunch of different ways and can't get this to work. I need to get the max date from a sheet to feed it into my meta data sheet (dates are stored in d…
    User: "susanmgfin"
    Answered ✓
    8
    2
  • I need help to come up with the risk formula. I tried many different formulas and tried to modify it but just can't seem to have desired results. So these are the conditions I MUST meet: Program is a …
    User: "Ronak"
    Answered ✓
    29
    6