SOLVED

=NETWORKDAYS help

Copper Contributor

Hello people,

 

I am trying to calculate how many times until or after a deadline, using the =networkdays function to exclude weekends. 

 

Example:

Today's date                                               Deadline                                       Days
Thursday the 17th of February       Wednesday the 23th of February          4 days before deadline (or -4

Wednesday the 23th of February   Wednesday the 23th of February          0 days before deadline (or 0)

Friday the 25th of February           Wednesday the 23th of February          2 days after deadline (or 2      

 

The unfornate thing is that =NETWORKDAYS includes today's day, meaning they return the values as -5, 1 and 2 in the above examples. A simple fix would be something like ''=NETWORKDAYS(A2; C2)-1''.

 

But the question is: How do I adjust the formula so that it adds ''1 day'' if the value returned is a negative number, and substract ''1 day'' if the value is returned as a positive number? I suppose it's with IF but I have no idea how to formulate the formula. Anybody can help?

5 Replies
best response confirmed by PAtesting (Copper Contributor)
Solution

@PAtesting I believe this one would work:

=NETWORKDAYS(A2;B2)+SIGN(A2-B2)

 

Thanks Riny, but it returns ##############. Do you happen to know the dutch formula? I assume you're Dutch, judging by your name

@PAtesting 

That would be

 

=NETTO.WERKDAGEN(A2;B2)+POS.NEG(A2-B2)

 

Format the cell with the formula as General (Standaard)

@PAtesting Met dank aan @Hans Vogelaar voor de vertaling!

Dankjulliewel, heren!
1 best response

Accepted Solutions
best response confirmed by PAtesting (Copper Contributor)
Solution

@PAtesting I believe this one would work:

=NETWORKDAYS(A2;B2)+SIGN(A2-B2)

 

View solution in original post