Formula question

DYou

I have a column ("Milestone Variance") that calculates the difference in days between two other columns ("Milestone Target Date" and "Milestone Completion Date").  

I have another column ("Milestone Completion Status") that looks at the Milestone Variance column and populates cells based on the Milestone Variance such that: 

variances <0 - "Early"; 

variances >0 = "Late"; and 

variances =0 = "On Time"

The formula is:=IF([Milestone Variance]@row  = 0, "On Time", IF([Milestone Variance]@row  < 0, "Early", IF([Milestone Variance]@row  > 0, "Late", "")))

The formula works except when there is a blank value for Milestone Variance. In instances where I only have a completion date and no target date, the value is blank, which is what I want.  However, the Milestone Completion Status automatically fills in the cell with "On Time" instead of also leaving it blank.  I tried to correct this with this formula:

=IF(ISBLANK[Milestone Variance]@row ), "", IF([Milestone Variance]@row = 0, "On Time", IF([Milestone Variance]@row < 0, "Early", IF([Milestone Variance]@row > 0, "Late", "")))

When I tried that formula, I got a message that says the column syntax isn't quite right.

Do you have a suggestion for how to fix this so that if the value in the reference cell is blank the value in the Milestone Completion cell will also be blank, but will return "early" if <0; "late" if >0, or "on time" if = 0?

Thanks in advance!

jmyzk_cloudsmart_jp

Hi @DYou

Your formula had a syntax error in ISBLANK[Milestone Variance]@row — square brackets must be used inside the function like ISBLANK([Column Name]@row ).

Second, there are four IF statements, so you must have four closing parentheses ). If even one is missing, Smartsheet will throw a syntax error.

However, we can simplify the logic slightly since the final case (greater than 0) is the only remaining possibility. Here’s the simplified and clean version (with only 3 IFs and 3 closing )): ( →My second formula)

Yours

=IF(ISBLANK[Milestone Variance]@row ), "", IF([Milestone Variance]@row = 0, "On Time", IF([Milestone Variance]@row < 0, "Early", IF([Milestone Variance]@row > 0, "Late", "")))

Mine

=IF(ISBLANK([Milestone Variance]@row ), "", IF([Milestone Variance]@row = 0, "On Time", IF([Milestone Variance]@row < 0, "Early", IF([Milestone Variance]@row > 0, "Late", ""))))

or

=IF(ISBLANK([Milestone Variance]@row ), "", IF([Milestone Variance]@row = 0, "On Time", IF([Milestone Variance]@row < 0, "Early", "Late")))

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

jmyzk_cloudsmart_jp

Hi @DYou

Your formula had a syntax error in ISBLANK[Milestone Variance]@row — square brackets must be used inside the function like ISBLANK([Column Name]@row ).

Second, there are four IF statements, so you must have four closing parentheses ). If even one is missing, Smartsheet will throw a syntax error.

However, we can simplify the logic slightly since the final case (greater than 0) is the only remaining possibility. Here’s the simplified and clean version (with only 3 IFs and 3 closing )): ( →My second formula)

Yours

=IF(ISBLANK[Milestone Variance]@row ), "", IF([Milestone Variance]@row = 0, "On Time", IF([Milestone Variance]@row < 0, "Early", IF([Milestone Variance]@row > 0, "Late", "")))

Mine

=IF(ISBLANK([Milestone Variance]@row ), "", IF([Milestone Variance]@row = 0, "On Time", IF([Milestone Variance]@row < 0, "Early", IF([Milestone Variance]@row > 0, "Late", ""))))

or

=IF(ISBLANK([Milestone Variance]@row ), "", IF([Milestone Variance]@row = 0, "On Time", IF([Milestone Variance]@row < 0, "Early", "Late")))

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

DYou

Problem solved - thank you so much!

jmyzk_cloudsmart_jp

Happy to help!😁