Column formula randomly not giving invalid value

MEJ

Help I have a column formula that is to calculate the when the team needs to reevaluate a row. This needs to happen every 6 months. The formula works for most rows but there are some that are now showing invalid value - this formula has been set to a column formula when the sheet was created so I am very confused.

Date Column is formatted with date mm/dd/yr

Formula is:

=DATE(YEAR([Screened dated]@row) + INT((MONTH([Screened dated]@row) + 6) / 12), MOD(MONTH([Screened dated]@row) + 6, 12), DAY([Screened dated]@row))

Can anyone help me to fix the formula so it stays working for every row? or explain why it is working for most rows but not all randomly?

I will add with a deeper review this error is only happening to entries made in June 2025 that should populate a December date. the Formula does show to work properly for crossing years Dec 24- June 25

Thank you.

TVang

@MEJ , let's change it bit. Use the following…

=DATE(YEAR([Screened dated]@row) + IF(MONTH([Screened dated]@row) <= 6, 0, 1), IF(MONTH([Screened dated]@row) = 6, 12, MOD(MONTH([Screened dated]@row) + 6, 12)), DAY([Screened dated]@row))

Here, we're still using IF(MONTH([Screened dated]@row) = 6, 12, MOD(MONTH([Screened dated]@row) + 6, 12)) to find the month "6 months from [Screened dated]".

The year "6 months from [Screened dated]" is YEAR( [Screened dated] ) when the month is from January-June. If the month is after June, then the year is YEAR([Screened dated]@row) + 1 . Essentially, YEAR = YEAR() + (0 or 1).

YEAR([Screened dated]@row) + IF(MONTH([Screened dated]@row) <= 6, 0, 1)

Verify the column names used in the formula if you continue to get UNPARSABLE.

Cheers!

Paul Newcome

@MEJ Sorry about that. My fingers weren't in tune with my brain apparently, and I had a typo.

=IFERROR(DATE(YEAR([Screened Date]@row), MONTH([Screened Date]@row) + 6, DAY([Screened Date]@row)), DATE(YEAR([Screened Date]@row) + 1, MONTH([Screened Date]@row) - 6, DAY([Screened Date]@row)))

TVang

@MEJ ,your formula needs to account specifically for when [Screen dated] is in the month of June.

When the date is in June, the expression MOD(MONTH([Screened dated]@row) + 6, 12) evaluates to "0". This is the reason why your formula is erroring out. (6 + 6) ÷ 12 = 12 remainder 0. Modify the expression to…

IF(MONTH([Screened dated]@row) = 6, 6, MOD(MONTH([Screened dated]@row) + 6, 12))

Hope this helps!

MEJ

@TVang your explanation makes complete sense and i appreciate the assist.

I have tried al sorts of ways and getting either incorrect argument, invalid value or occasionally unparsable.

If I only put in your line of formula it populates 6

If I add in the formula to existing formula- incorrect argument

If I change out the

 INT((MONTH([Screened dated]@row) + 6) / 12), MOD(MONTH([Screened dated]@row) + 6, 12 to what you say it says unparsable

I know I am missing something I just have hit a wall and can't find it😒

TVang

@MEJ , let's change it bit. Use the following…

=DATE(YEAR([Screened dated]@row) + IF(MONTH([Screened dated]@row) <= 6, 0, 1), IF(MONTH([Screened dated]@row) = 6, 12, MOD(MONTH([Screened dated]@row) + 6, 12)), DAY([Screened dated]@row))

Here, we're still using IF(MONTH([Screened dated]@row) = 6, 12, MOD(MONTH([Screened dated]@row) + 6, 12)) to find the month "6 months from [Screened dated]".

The year "6 months from [Screened dated]" is YEAR( [Screened dated] ) when the month is from January-June. If the month is after June, then the year is YEAR([Screened dated]@row) + 1 . Essentially, YEAR = YEAR() + (0 or 1).

YEAR([Screened dated]@row) + IF(MONTH([Screened dated]@row) <= 6, 0, 1)

Verify the column names used in the formula if you continue to get UNPARSABLE.

Cheers!

Paul Newcome

Try this:

=IFERROR(DATE(YEAR([Screened Date]@row), MONTH([Screened Date]@row), DAY([Screened Date]@row)), DATE(YEAR([Screened Date]@row) + 1, MONTH([Screened Date]@row) - 6, DAY([Screened Date]@row)))

MEJ

@TVang you are amazing it finally is working like it should. Thank you for your brain power and time.

@Paul Newcome Thank you for your suggestion as well I did try it just to see and unfortunately is was not changing anything. Thank you for your input and assistance.

Thank you smartsheet community for the help and not making me feel stupid for asking. 😉

Paul Newcome

@MEJ Sorry about that. My fingers weren't in tune with my brain apparently, and I had a typo.

=IFERROR(DATE(YEAR([Screened Date]@row), MONTH([Screened Date]@row) + 6, DAY([Screened Date]@row)), DATE(YEAR([Screened Date]@row) + 1, MONTH([Screened Date]@row) - 6, DAY([Screened Date]@row)))

TVang

Glad to be of help, @MEJ.

And @Paul Newcome 's approach is spot on, too. It reminds us that, in terms of a 6-month cycle, "6 months from NOW" is also "6 months AGO". For example, if you count 6 months from January—forward (+6) or backward (-6)—you'll always end up at July. (I, too, had to count with fingers to confirm. 🙂)

Paul Newcome

I tend to use this method more often than not because it is easy to adjust for +/- up to 11 months. 12 months is just adding or subtracting one year of course, and more than 12 months I have another solution for, but in this method, just make sure your two numbers add up to 12 and the sign is flipped from first to second.

Adding four months would be +4/-8. Adding 7 months would be +7/-5. Subtracting three months is -3/+9 (and of course subtracting instead of adding a year in the second one).