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

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

Find more posts tagged with

Formulas

Comments

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.

Sign in to join the conversation!

Quick Links

Trending discussions

Netdays Formula
I am wanting to calculate Netdays but only if there is data in 'Corrective Action 1' I am currently using this formula: =IF(ISBLANK([Date Action 1 Complete]@row ), NETDAYS([Date of Event]@row , TODAY()), NETDAYS([Date of Event]@row , [Date Action 1 Complete]@row )) How do I adjust it to only pull a date when Corrective…
Re-write a formula to make it Column Formula compatible
I have Concept phase projects that can result in multiple Phase 1 projects being generated from one Concept Review. I want to calculate the time in the Concept Phase for the first project from that Concept (the Approved Gate 0 UID), but mark it N/A for any subsequent references to that UID. I figured out the formula below…
How to split contents from a cell from a smartsheet into another smartsheet
I need to extract and split the contents of a cell in a Smartsheet into three separate columns to a new smartsheet. The cell entries are originally submitted through a form.