Counting for a Blank Cell
Hello!
I have this formula that works great:
=DATE(YEAR([Completion Date]3), (MONTH([Completion Date]3) + 3), (DAY([Completion Date]3)))
But if my date in "[Completion Date]3" is blank it throws off my whole formula. What can I add so it stays blank if I have no date in "[Completion Date]3".
THANK YOU!
Comments
-
Hi Lisi,
You need to add an IFERROR function.
More info: https://help.smartsheet.com/function/iferror
Let me know if you have any questions!
Have a fantastic week!
Best,
Andrée Starå
Workflow Consultant @ Get Done Consulting
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
To be more specific you can wrap the entire formula in the IFerror function like this...
=IFERROR(DATE(YEAR([Completion Date]3), (MONTH([Completion Date]3) + 3), (DAY([Completion Date]3))),"Insert Date")
This will give you the value of "Insert Date" if there is no date in the Completion Date field. If you want it to be blank, you can Delete the text Insert Date and leave the quotation marks.
-
Hi Mike,
Thank you very much that works perfectly!
-
You're welcome. Glad I could be of assistance.
-
You could also use your current formula in an IF statement to say if the completion date is an actual date, then run your formula, otherwise leave blank.
=IF(ISDATE([Completion Date]3), your formula goes here, "")
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!
-
So true Paul! That's a good idea too.
-
This is perfect, I was able to answer another question with this also!
THANK YOU PAUL!
-
I use this process a lot to build nested IF statements. Sure you could use an AND statement if you have multiple requirements, but a lot of the time I haven't realized the need for it until I have already built the bulk of my formula. In those cases, I'll just wrap it in one more IF function. This also provides the ability to account for things other than errors.
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!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!