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