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

JimT15503

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?

Mike Wilday

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)

J. Craig Williams

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

 

 

 

JimT15503

Thanks - big help!

JimT15503

Thanks for your reply