Help with Networkdays formula

steve50951 · 08/01/19

i have a vacation calendar sheet. I am using the networkdays formula to calculate vacation days

=NETWORKDAYS([Start Date]10, [End Date]10).

The problem is that if there is not a start date and and end date entered into the cell it comes back with an "invalid data type." I am also using a formula to calculate remaining vacation days.

=SUM([Vacation Days Allowed]@row - [Vacation Days]@row)

When the Networkdays formula returns an "invalid data type" it also causes the remaining vacation days formula to come back with the same.

I need to be able to have it to where if the start date and end date are blank it wont return an invalid data response.

I've attached a few screenshots to illustrate.

Thanks

Screen Shot 2019-08-01 at 9.15.43 AM.png

Screen Shot 2019-08-01 at 9.15.56 AM.png

Screen Shot 2019-08-01 at 9.16.10 AM.png

Screen Shot 2019-08-01 at 9.16.33 AM.png

Paul Newcome · 08/01/19

Just wrap the NETWORKDAYS formula in an IFERROR function.

=IFERROR(NETWORKDAYS(....................), "")

This will replace the error message with a blank cell.

steve50951 · 08/01/19

ah...of course!

Thanks!

SGF

steve50951 · 08/01/19

so i got the IFERROR to work, but i have 1 more question...

I want to add 1 more condition to the formula. I want it to read 0 if "Time Off Without Pay" is in the "type of absence" column

Here's what i have, but it comes back as unparseable

=IF([Type of Absence Request]@row = "Time Off Without Pay", 0), OR(IFERROR(NETWORKDAY([Start Date]@row, [End Date]@row, "")))

Ive tried AND, as well, but it doesnt seem to work either.

Any suggestions?

Thanks!

Steve

steve50951 · 08/01/19

So this formula seems to work, do you see any issues with it, long term?

=IF([Type of Absence Request]@row = "Time Off Without Pay", 0, IFERROR(NETWORKDAY([Start Date]@row, [End Date]@row), ""))

thanks for you help!

Paul Newcome · 08/01/19

Close. It's just a little syntax issue. Uou don't need to use the OR or AND functions in this case.

Here's a breakdown of the IF syntax:

=IF(this is true, do this, otherwise that)

.

Now lets look at what you are trying to do and fill in the blanks.

Part 1:

=IF(this is true,

=IF([Type of Absence Request]@row = "Time Off Without Pay",

.

Part 2:

do this,

=IF([Type of Absence Request]@row = "Time Off Without Pay", 0,

.

Part 3:

otherwise do that)

=IF([Type of Absence Request]@row = "Time Off Without Pay", 0, IFERROR(NETWORKDAYS([Start Date]@row, [End Date]@row, ""))

Paul Newcome · 08/01/19

Looks like we were typing at the same time. Haha.

Looks good to me.

steve50951 · 08/02/19

Paul,

Thanks for breaking that down for me! Makes more sense when you explain it that way. I really appreciate all of your help!

World class man!

Have a great weekend!

Steve

Paul Newcome · 08/02/19

Happy to help!

I try to break things down for people when I can because I found that breaking things down really helped me when I was getting more involved in formulas.

Help with Networkdays formula

Comments

Help Article Resources

Categories