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
-
@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
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!
-
@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
-
@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
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!
-
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))
-
Jeff, That worked. How would I do this if the cells are not dates?
-
@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))
-
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))
-
@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)
-
Awesome, thanks Paul!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!