NETWORKDAYS FORMULA

Sidonia Posteuca

Hello,

I am trying to construct a formula to calculate the number of business days between two date columns (date received and date entered into system, for example) within a specific date range (from 02/18/2022 - 03/18/2022).

How can I accomplish this if at all possible.

Please help. Thank you!

Jeff Reisman

Which column or columns have your date range?

You essentially need an IF or IF/AND to determine if the date(s) fall into the desired range, then have a NETWORKDAYS as the positive condition. Something like this:

=IF(AND(DateEntered@row > DATE(2022, 2, 18), DateReceived@row < DATE(2022, 3, 18)), NETWORKDAYS(DateEntered@row, Datereceived@row), "")

Sidonia Posteuca

The formula worked! Thank you.

On another note, is there a way that you can tell a cell to remain blank rather than provide an error message (i.e., divide by zero) so that if you have multiple formulas relying on each other, kind of like a domino effect, it won't disable the following formulas from working.

Jeff Reisman

https://community.smartsheet.com/discussion/comment/321063#Comment_321063

Glad it worked.

The IFERROR function can help you with that. The syntax is:

=IFERROR(your formula goes here), alternate value if there's an error)

In use:

=IFERROR(IF(AND(DateEntered@row > DATE(2022, 2, 18), DateReceived@row < DATE(2022, 3, 18)), NETWORKDAYS(DateEntered@row, Datereceived@row), ""), "These aren't the droids you're looking for.")

Or you can just use , "") at the end, for a blank value.

Sidonia Posteuca

Thank you! You're a genius.