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)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
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))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Awesome, thanks Paul!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 438 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 451 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 283 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!