Help with Networkdays formula
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
Comments
-
Just wrap the NETWORKDAYS formula in an IFERROR function.
=IFERROR(NETWORKDAYS(....................), "")
This will replace the error message with a blank cell.
-
ah...of course!
Thanks!
SGF
-
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
-
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, ""))
-
Looks like we were typing at the same time. Haha.
Looks good to me.
-
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
-
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 Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!