Getting #UNPARSEABLE after using IFERROR

04/21/21
Accepted

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), "-")

Popular Tags:

Best Answer

  • Paul NewcomePaul Newcome ✭✭✭✭✭
    Accepted Answer

    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, "-")

Answers

  • Paul NewcomePaul Newcome ✭✭✭✭✭
    Accepted Answer

    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, "-")

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

    Happy to help. 👍️


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

Sign In or Register to comment.