Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Need a How-To: IF [blank addends], blank, IF [values], sum

edited 12/09/19

I'm working with this formula, which gives me [Expected Completion Date]:

=[Planned Start Date]1 + [Expected Days Duration]1

Without values in Planned Start or Expected Days, I get #INVALID OPERATION in the Expected Completion.  What's the simplest IF/THEN to use that will return a blank in EXPECTED COMPLETION if we haven't yet entered values in either or both of the other two cells, and returning the original sum from above when there are values in both?

• ✭✭✭✭✭✭

Try this one. If either Planned start date or Expected Days duration is blank you should receive a blank. Otherwise, you will get your calculation.

=IF(OR(ISblank([Planned Start Date]1),(ISBLANK([Expected Days Duration]1)), "", =[Planned Start Date]1 + [Expected Days Duration]1)

• ✭✭✭✭✭✭

This is a bit simpler:

=IFERROR([Planned Start Date]23 + [Expected Days Duration]23, "")

I assume:

[Expected Days Duration] is a Text/Number type column. A blank or non-number value should not throw an error. Only blanks in the Date column will.

However, there is a slight nuance to adding a number of days to a date. Because the [Expected Days Duration] is a Text/Number column, it can be Text.

07/24/17 + B is 07/24/17B

Likely an error, and one that is hard to catch.

If your [Expected Completion Date] needs to fall on a working day, then you are in luck.

This will throw an error (which will result in a blank) when the Date is blank or if the Duration is not a number and it will skip non-working days.

=IFERROR(WORKDAY([Planned Start Date]23, [Expected Days Duration]23), "")

Craig

• Thanks - big help!

• edited 08/08/17