Getting #UNPARSEABLE after using IFERROR

Michaela Kamenska

Hi all,

I've got this awesome formula (Thanks, Paul Newcome!), which calculates the difference in months between two dates:

=(12 - MONTH([Actual Study End Date]@row) + (((YEAR([Expected Expiration Date]@row) - YEAR([Actual Study End Date]@row)) * 12) - 12) + MONTH([Expected Expiration Date]@row)) + ((((IFERROR(DATE(YEAR([Actual Study End Date]@row), MONTH([Actual Study End Date]@row) + 1, 1), DATE(YEAR([Actual Study End Date]@row) + 1, 1, 1)) - 1) - [Actual Study End Date]@row) + 1) / DAY(IFERROR(DATE(YEAR([Actual Study End Date]@row), MONTH([Actual Study End Date]@row) + 1, 1), DATE(YEAR([Actual Study End Date]@row) + 1, 1, 1)) - 1)) + (DAY([Expected Expiration Date]@row) / DAY(IFERROR(DATE(YEAR([Expected Expiration Date]@row), MONTH([Expected Expiration Date]@row) + 1, 1), DATE(YEAR([Expected Expiration Date]@row) + 1, 1, 1)) - 1)) - 1

This formula works great if both "Expected Expiration Date" and "Actual Study End Date" are populated, however, I will get an error #INVALID DATA TYPE if one or both of these cells are not populated.

I tried adding IFERROR argument as below, however, I will get #UNPARSEABLE. Can someone advise where the syntax issue is? I've not had a problem with IFERROR until now.

=IFERROR((12 - MONTH([Actual Study End Date]@row) + (((YEAR([Expected Expiration Date]@row) - YEAR([Actual Study End Date]@row)) * 12) - 12) + MONTH([Expected Expiration Date]@row)) + ((((IFERROR(DATE(YEAR([Actual Study End Date]@row), MONTH([Actual Study End Date]@row) + 1, 1), DATE(YEAR([Actual Study End Date]@row) + 1, 1, 1)) - 1) - [Actual Study End Date]@row) + 1) / DAY(IFERROR(DATE(YEAR([Actual Study End Date]@row), MONTH([Actual Study End Date]@row) + 1, 1), DATE(YEAR([Actual Study End Date]@row) + 1, 1, 1)) - 1)) + (DAY([Expected Expiration Date]@row) / DAY(IFERROR(DATE(YEAR([Expected Expiration Date]@row), MONTH([Expected Expiration Date]@row) + 1, 1), DATE(YEAR([Expected Expiration Date]@row) + 1, 1, 1)) - 1)) - 1), "-")

Paul Newcome

You are adding in a closing parenthesis that isn't needed.

=IFERROR(original_formula, "-")

=IFERROR((12 - MONTH([Actual Study End Date]@row) + (((YEAR([Expected Expiration Date]@row) - YEAR([Actual Study End Date]@row)) * 12) - 12) + MONTH([Expected Expiration Date]@row)) + ((((IFERROR(DATE(YEAR([Actual Study End Date]@row), MONTH([Actual Study End Date]@row) + 1, 1), DATE(YEAR([Actual Study End Date]@row) + 1, 1, 1)) - 1) - [Actual Study End Date]@row) + 1) / DAY(IFERROR(DATE(YEAR([Actual Study End Date]@row), MONTH([Actual Study End Date]@row) + 1, 1), DATE(YEAR([Actual Study End Date]@row) + 1, 1, 1)) - 1)) + (DAY([Expected Expiration Date]@row) / DAY(IFERROR(DATE(YEAR([Expected Expiration Date]@row), MONTH([Expected Expiration Date]@row) + 1, 1), DATE(YEAR([Expected Expiration Date]@row) + 1, 1, 1)) - 1)) - 1, "-")

Paul Newcome

You are adding in a closing parenthesis that isn't needed.

=IFERROR(original_formula, "-")

=IFERROR((12 - MONTH([Actual Study End Date]@row) + (((YEAR([Expected Expiration Date]@row) - YEAR([Actual Study End Date]@row)) * 12) - 12) + MONTH([Expected Expiration Date]@row)) + ((((IFERROR(DATE(YEAR([Actual Study End Date]@row), MONTH([Actual Study End Date]@row) + 1, 1), DATE(YEAR([Actual Study End Date]@row) + 1, 1, 1)) - 1) - [Actual Study End Date]@row) + 1) / DAY(IFERROR(DATE(YEAR([Actual Study End Date]@row), MONTH([Actual Study End Date]@row) + 1, 1), DATE(YEAR([Actual Study End Date]@row) + 1, 1, 1)) - 1)) + (DAY([Expected Expiration Date]@row) / DAY(IFERROR(DATE(YEAR([Expected Expiration Date]@row), MONTH([Expected Expiration Date]@row) + 1, 1), DATE(YEAR([Expected Expiration Date]@row) + 1, 1, 1)) - 1)) - 1, "-")

Michaela Kamenska

Thanks Paul,

I tried both versions at first (including one similar to yours) but none worked. I must have missed something there too! (facepalm)

Thanks again!

Paul Newcome

Happy to help. 👍️

Those parenthesis can get to be a pain at times. Haha