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

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)))

Best Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓

    @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.

    Regards,

    Jeff Reisman, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    @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)

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓

    @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.

    Regards,

    Jeff Reisman, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Paul Newcome
    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
    Max Ahmed ✭✭✭✭

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

  • Paul Newcome
    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
    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
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    @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
    Max Ahmed ✭✭✭✭

    Awesome, thanks Paul!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!