How to show blank where no data is, when using =MAX(0, NETWORKDAYS(TODAY(), End@row))

Christopher Warren

James Keuning

iferror(MAX(0, NETWORKDAYS(TODAY(), End@row)),"")

Jeff Reisman

@Chris Wash

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.

Christopher Warren

I would imagine I need to add an IFERROR, just can't figure out where .

James Keuning

iferror(MAX(0, NETWORKDAYS(TODAY(), End@row)),"")

Christopher Warren

@James Keuning

Thanks so much

Jeff Reisman

@Chris Wash

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.

Christopher Warren

@Jeff Reisman

Thanks for the information, very helpful

Christopher Warren

@Jeff Reisman

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"

Jeff Reisman

https://community.smartsheet.com/discussion/comment/329419#Comment_329419

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.