Syntax error for nested IF statements

uqeradun

Hi there,

I am looking to use a column formula to calculate review dates based on a few criteria.

-If the 'Date of last review with no changes' is blank I want it to progress to the next IF statement:

-If the 'Effective Date' is text I want it to return the 'Effective Date'

-If the 'Effective Date is NOT text I want it to progress to the next IF statement:

-If the 'Review period (years)' is text I want it to return the 'Review period (years)'

-If the 'Review period (years)' is NOT text I want it to calculate 'Effective Date' + ('Review period (years)' * 365)

-If the 'Date of last review with no changes' is NOT blank I want it to calculate 'Date of last review with no changes' + ('Review period (years)' * 365)

The formula I have devised is:

=IF(ISBLANK[Date of last review with no changes]@row, (IF(ISTEXT[Effective Date]@row, [Effective Date]@row, (IF(ISTEXT[Review period (years)]@row, [Review period (years)]@row, [([Effective Date]@row) + ([Review period (years)]@row * 365)])))), [([Date of last review with no changes]@row) + ([Review period (years)]@row * 365)])

But it is coming up with a syntax error. All the parentheses seem to be the correct colour and matching up correctly, and each part of the formula seems to be identified correctly (the pop-up box is defining it as the correct part (i.e. logical expression, value if true, value if false)), except for the last 'value if false' part which is not coming up with any label when I click on it.

Can anyone help me fix this?

Thanks,

Eve

uqeradun

Okay I solved it haha.

This is the working formula:

=IF(ISBLANK([Date of last review with no changes]@row), (IF(ISTEXT([Effective Date]@row), [Effective Date]@row, (IF(ISTEXT([Review period (years)]@row), [Review period (years)]@row, ([Effective Date]@row + ([Review period (years)]@row * 365)))))), ([Date of last review with no changes]@row + ([Review period (years)]@row * 365)))

I don't know how it is different from what I had initially but I hope it helps somebody at some point.

uqeradun

By the way this is based on a formula that was working:

=IF(ISTEXT([Effective Date]@row), [Effective Date]@row, (IF(ISTEXT([Review period (years)]@row), [Review period (years)]@row, ([Effective Date]@row + ([Review period (years)]@row * 365)))))

I just want to add in the initial if statement about the 'Date of last review with no changes' column.

uqeradun

Okay I solved it haha.

This is the working formula:

=IF(ISBLANK([Date of last review with no changes]@row), (IF(ISTEXT([Effective Date]@row), [Effective Date]@row, (IF(ISTEXT([Review period (years)]@row), [Review period (years)]@row, ([Effective Date]@row + ([Review period (years)]@row * 365)))))), ([Date of last review with no changes]@row + ([Review period (years)]@row * 365)))

I don't know how it is different from what I had initially but I hope it helps somebody at some point.

Georgie

Hey @uqeradun,

Thank you for posting your solution. I'm glad you got this resolved!

Georgie