Help with Networkdays formula

steve50951

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

Paul Newcome

Just wrap the NETWORKDAYS formula in an IFERROR function.

 

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

 

This will replace the error message with a blank cell.

steve50951

ah...of course!

Thanks!

SGF

steve50951

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

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

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

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

 

Looks good to me. 

steve50951

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

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.