How to show blank where no data is, when using =MAX(0, NETWORKDAYS(TODAY(), End@row))
Best Answers
-
iferror(MAX(0, NETWORKDAYS(TODAY(), End@row)),"")
-
There is another method that can come in handy, especially when you have complex formulas, where IFERROR may not be practical.
You can use IF to give the formula a condition that must be met before it tries the formula, so that it does not try to operate on incompatible cells (like blank ones.) In your case, you don't want your NETWORKDAYS to try to evaluate a cell that is not a date value, because it throws an error. You can use IF and ISDATE to evaluate the "End" cell first to see if it is a date value, and if so, run your MAX formula (and if not, just leave the cell blank):
=IF(ISDATE(End@row), MAX(0, NETWORKDAYS(TODAY(), End@row)), "")
The difference between using IFERROR and using IF ISDATE is that IFERROR suppresses error messages, while IF ISDATE prevents the errors.
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!
Answers
-
I would imagine I need to add an IFERROR, just can't figure out where .
-
iferror(MAX(0, NETWORKDAYS(TODAY(), End@row)),"")
-
Thanks so much
-
There is another method that can come in handy, especially when you have complex formulas, where IFERROR may not be practical.
You can use IF to give the formula a condition that must be met before it tries the formula, so that it does not try to operate on incompatible cells (like blank ones.) In your case, you don't want your NETWORKDAYS to try to evaluate a cell that is not a date value, because it throws an error. You can use IF and ISDATE to evaluate the "End" cell first to see if it is a date value, and if so, run your MAX formula (and if not, just leave the cell blank):
=IF(ISDATE(End@row), MAX(0, NETWORKDAYS(TODAY(), End@row)), "")
The difference between using IFERROR and using IF ISDATE is that IFERROR suppresses error messages, while IF ISDATE prevents the errors.
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!
-
Thanks for the information, very helpful
-
Sorry to bother but I thought you might be the best to ask:
I'm trying to have the Status column drop down pick Processing if date is after today's date & trying to have the Status column drop down pick LATE if date is before today's date.
But if the date is blank, what would need to be added to the function to have the drop down not choose anything?
Would there need to be an "=IF(ISDATE"
-
You could take your existing formula and use it as the positive condition of an IF(ISDATE.
=IF(ISDATE([Estimated Completion Date]@row), your current formula, "")
If it's a date, run this IF, if it's not a date, just leave it 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!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!