Hide #INVALID DATA
Hello,
I am attempting to calculate the difference between two dates. When one of the dates is missing, the cell where the difference would be shown instead contains "#INVALID DATA".
Can anyone please help? I've attached a screenshot. The "Actual Duration" column is the difference between the "Start Date" and "End Date". I would like to have the cell in "Actual Duration" remain blank when the "End Date" cell is blank.
Thanks,
Jonny
Comments
-
Hi Jonny,
Does this work?
=IF(ISBLANK([End Date]1), " ", [End Date]1 - [Start Date]1)
Thanks,
Dave
-
You can also do this:
=IFERROR([End Date]1 - [Start Date]1, "")
And if you want to make that formula more independent use @row instead of a physical reference.
=IFERROR([End Date]@row - [Start Date]@row, "")
For more on these formulas see:
- https://help.smartsheet.com/function/iferror
- https://help.smartsheet.com/articles/2476491-create-efficient-formulas-with-at-cell#row
-
Hi Mike,
Thanks for sharing the iferror function. I had never seen it before. That's the cool thing about the community, almost every time I share how I do something I learn that there's a better way!
Dave
-
You're welcome. It is fun to learn from others! I've done my fair share of learning these past couple of years. I'm glad I can be an inspiration!
Jonny, did either of our solutions work for you?
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives