Help with ISBLANK on column formula

Kathy McFarland

I am stuck getting this to work and have been trying both ISERROR and ISBLANK but keep getting an UNPARSEABLE error. Here is the overview of what I need to do:

First, I have a column [Cumulative Delay for Start Date] that increments based on other values for a row. The second column is the base starting date [CALC DATE] that comes from a date modified field. The [CALC DATE] is returned from the automation when the user selects 'Offer Letter' under the [Final Outcome] column. Only when 'Offer Letter' is selected do we compute the Start Date. I need to determine the start date for the applicant beginning with the [CALC DATE] + 42 days + the value in [Cumulative Delay for Start Date].

So long as there is a valid date in [CALC DATE], no issue with this formula:

=([CALC DATE]@row + 42 + [Cumulative Delay for Start Date]@row)

However, if the [CALC DATE] field is blank and a column formula is attempted, it calculates what appears to be 42 appended to the [Cumulative Delay for Start Date]. So the Start Date fills with values like '42120', '420', '42240', etc.

The sections in green are where the formula woks appropriately because there is a valid date in the [CALC DATE] field when 'Offer Letter' has started the trigger.

When an application is incomplete and I leave that column formula in place, then you get the items in red boxes.

I have tried to adjust the column formula to use ISBLANK like so:

=IF(ISBLANK([CALC DATE]@ROW, “”,[CALC DATE]@row + 42 + [Cumulative Delay for Start Date]@row))

I hope you can help me with this. Basically, I don't want the calculation to happen unless there is a valid date in the [CALC DATE] column for the row. What am I doing wrong?

jmyzk_cloudsmart_jp

Hi @Kathy McFarland

A quick answer to your question is that you forgot to close the ISBLANK function with the ")".

Your formula

=IF(ISBLANK([CALC DATE]@ROW, “”,[CALC DATE]@row + 42 + [Cumulative Delay for Start Date]@row))

Correct Formula

=IF(ISBLANK([CALC DATE]@row), "", [CALC DATE]@row + 42 + [Cumulative Delay for Start Date]@row)

At first, I was not able to recreate your formula error. But when I used a formula that set the CALC DATE value as "" or a text value, I was able to recreate your error. So, changing your formula for the CALC DATE to not to return "" may also solve the issue.

Instead of using the ISBLANK function, you can use the ISDATE function. (See [Start Date 2])

However, as your sheet has formulas and automation to set values, if the FINAL OUTCOME is "Offer Letter," you can use that as the IF statement's condition. (See [Start Date 1])

Here are a couple of formulas that you can reference;

[CALC DATE] =IF([Final Outcome]@row = "Offer Letter", IF([Cumulative Delay for Start Date]@row = 120, TODAY(), IF([Cumulative Delay for Start Date]@row = 240, TODAY(-10))), "")
[Start Date] =[CALC DATE]@row + 42 + [Cumulative Delay for Start Date]@row
[Start Date 0] =IF(ISBLANK([CALC DATE]@row), "", [CALC DATE]@row + 42 + [Cumulative Delay for Start Date]@row)
[Start Date 1] =IF([Final Outcome]@row = "Offer Letter", [CALC DATE]@row + 42 + [Cumulative Delay for Start Date]@row)
[Start Date 2] =IF(ISDATE([CALC DATE]@row), [CALC DATE]@row + 42 + [Cumulative Delay for Start Date]@row)
[Start Date 3] =IF(AND([Final Outcome]@row = "Offer Letter", ISDATE([CALC DATE]@row)), [CALC DATE]@row + 42 + [Cumulative Delay for Start Date]@row)

https://app.smartsheet.com/b/publish?EQBCT=7a4d5d3752fd4d30bd6f7cd1e2f335f6

jmyzk_cloudsmart_jp

Hi @Kathy McFarland

A quick answer to your question is that you forgot to close the ISBLANK function with the ")".

Your formula

=IF(ISBLANK([CALC DATE]@ROW, “”,[CALC DATE]@row + 42 + [Cumulative Delay for Start Date]@row))

Correct Formula

=IF(ISBLANK([CALC DATE]@row), "", [CALC DATE]@row + 42 + [Cumulative Delay for Start Date]@row)

At first, I was not able to recreate your formula error. But when I used a formula that set the CALC DATE value as "" or a text value, I was able to recreate your error. So, changing your formula for the CALC DATE to not to return "" may also solve the issue.

Instead of using the ISBLANK function, you can use the ISDATE function. (See [Start Date 2])

However, as your sheet has formulas and automation to set values, if the FINAL OUTCOME is "Offer Letter," you can use that as the IF statement's condition. (See [Start Date 1])

Here are a couple of formulas that you can reference;

[CALC DATE] =IF([Final Outcome]@row = "Offer Letter", IF([Cumulative Delay for Start Date]@row = 120, TODAY(), IF([Cumulative Delay for Start Date]@row = 240, TODAY(-10))), "")
[Start Date] =[CALC DATE]@row + 42 + [Cumulative Delay for Start Date]@row
[Start Date 0] =IF(ISBLANK([CALC DATE]@row), "", [CALC DATE]@row + 42 + [Cumulative Delay for Start Date]@row)
[Start Date 1] =IF([Final Outcome]@row = "Offer Letter", [CALC DATE]@row + 42 + [Cumulative Delay for Start Date]@row)
[Start Date 2] =IF(ISDATE([CALC DATE]@row), [CALC DATE]@row + 42 + [Cumulative Delay for Start Date]@row)
[Start Date 3] =IF(AND([Final Outcome]@row = "Offer Letter", ISDATE([CALC DATE]@row)), [CALC DATE]@row + 42 + [Cumulative Delay for Start Date]@row)

https://app.smartsheet.com/b/publish?EQBCT=7a4d5d3752fd4d30bd6f7cd1e2f335f6

Kathy McFarland

Thank you OOOO much, @jmyzk_cloudsmart_jp! The missing parentheses strikes again! That absolutely fixed my columns formula.

=IF(ISBLANK([CALC DATE]@row), "", [CALC DATE]@row + 42 + [Cumulative Delay for Start Date]@row)

You have made my Monday. Appreciate the quick response.

Kathy