Formula if a certain cell is blank otherwise display the date in that cell.

Max Ahmed

What's wrong with his formula? I have also tried wrapping it in an if error. I am essentially trying to create a manual override for a date by formula. For example the formula inputs a start date of 1/23/24. If I manually enter a date into another Column I'd like it to show that date instead.

Here is the original formula that will spit a date out or a blank if there is no date in Construction Start

=IF(NOT(ISBLANK([Construction Start]@row), [Construction Start]@row + 21)

Here is the Formula that I am trying to achieve. If the GC start date is Blank the run the formula above. If it isn't blank then display the value.

=IF(ISBLANK([GC Start Date]@row), IF(NOT(ISBLANK([Construction Start]@row), [Construction Start]@row + 21), IF(NOT(ISBLANK([GC Start Date]@row)), [GC Start Date]@row)))

Jeff Reisman

@Max Ahmed Just so I'm clear, you're not trying to use this formula in the GC Start Date column, right? Formulas can't reference the cell they are in, so keep that in mind.

Sometimes it's best to flip your IFs around to begin with looking for a condition that exists, rather than one that doesn't. So I would start with what to do if there is a date in GC Start Date (using the ISDATE function,) and go from there:

=IF(ISDATE([GC Start Date]@row), [GC Start Date]@row, IF(ISDATE([Construction Start]@row), ([Construction Start]@row + 21), ""))

English: If there's a date value in GC Start Date, use that date value here; otherwise, check to see if there's a date in Construction Start, and if so, use that date value + 21 days here; if there's no date in Construction Start, leave this cell blank.

Paul Newcome

@Max Ahmed Am I correct in assuming that if the Manual Override is not blank then you want to output Manual Override?

If so, Try this instead:

=IF([MANUAL OVERRIDE - Address]@row <> "", [MANUAL OVERRIDE - Address]@row, [PIPELINE Address]@row)

Jeff Reisman

@Max Ahmed Just so I'm clear, you're not trying to use this formula in the GC Start Date column, right? Formulas can't reference the cell they are in, so keep that in mind.

Sometimes it's best to flip your IFs around to begin with looking for a condition that exists, rather than one that doesn't. So I would start with what to do if there is a date in GC Start Date (using the ISDATE function,) and go from there:

=IF(ISDATE([GC Start Date]@row), [GC Start Date]@row, IF(ISDATE([Construction Start]@row), ([Construction Start]@row + 21), ""))

English: If there's a date value in GC Start Date, use that date value here; otherwise, check to see if there's a date in Construction Start, and if so, use that date value + 21 days here; if there's no date in Construction Start, leave this cell blank.

Paul Newcome

It looks like you may have some misplaced parenthesis and extra pieces that are not needed.

Corrected parenthesis:

=IF(ISBLANK([GC Start Date]@row), IF(NOT(ISBLANK([Construction Start]@row)), [Construction Start]@row + 21, IF(NOT(ISBLANK([GC Start Date]@row)), [GC Start Date]@row)))

Condensed:

=IF(ISBLANK([GC Start Date]@row), IF(NOT(ISBLANK([Construction Start]@row)), [Construction Start]@row + 21, [GC Start Date]@row))

Further Condensed:

=IF([GC Start Date]@row="", IF([Construction Start]@row<>"", [Construction Start]@row + 21, [GC Start Date]@row))

Max Ahmed

Jeff, That worked. How would I do this if the cells are not dates?

Paul Newcome

@Max Ahmed Did you give the formula I posted a shot? It is essentially using blank vs not blank instead of date vs not date.

=IF([GC Start Date]@row="", IF([Construction Start]@row<>"", [Construction Start]@row + 21, [GC Start Date]@row))

Max Ahmed

Paul, I did not. This is the Effect I am trying to Achieve.

Basically IF the Manual Override address is blank then insert the Pipeline address. If they're Both blank leave it blank

=IF(MANUAL OVERRIDE - Address]@row="", IF([PIPELINE Address]@row<>"", [PIPELINE Address]@row, [MANUAL OVERRIDE Address]@row))

Paul Newcome

@Max Ahmed Am I correct in assuming that if the Manual Override is not blank then you want to output Manual Override?

If so, Try this instead:

=IF([MANUAL OVERRIDE - Address]@row <> "", [MANUAL OVERRIDE - Address]@row, [PIPELINE Address]@row)

Max Ahmed

Awesome, thanks Paul!