SOLVED

=NETWORKDAYS help

%3CLINGO-SUB%20id%3D%22lingo-sub-3202888%22%20slang%3D%22en-US%22%3E%3DNETWORKDAYS%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3202888%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20people%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20calculate%20how%20many%20times%20until%20or%20after%20a%20deadline%2C%20using%20the%20%3Dnetworkdays%20function%20to%20exclude%20weekends.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExample%3A%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CEM%3EToday's%20date%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BDeadline%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BDays%3CBR%20%2F%3E%3C%2FEM%3E%3C%2FSTRONG%3E%3CEM%3EThursday%20the%2017th%20of%20February%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BWednesday%20the%2023th%20of%20February%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%3CU%3E4%20days%20before%20deadline%20(or%20%3CSTRONG%3E-4%3C%2FSTRONG%3E)%26nbsp%3B%3C%2FU%3E%3C%2FEM%3E%3C%2FP%3E%3CP%3E%3CEM%3EWednesday%20the%2023th%20of%20February%26nbsp%3B%20%26nbsp%3BWednesday%20the%2023th%20of%20February%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%3CU%3E0%20days%20before%20deadline%20(or%20%3CSTRONG%3E0%3C%2FSTRONG%3E)%3C%2FU%3E%3C%2FEM%3E%3C%2FP%3E%3CP%3E%3CEM%3EFriday%20the%2025th%20of%20February%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BWednesday%20the%2023th%20of%20February%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%3CU%3E2%20days%20after%20deadline%20(or%20%3CSTRONG%3E2%3C%2FSTRONG%3E)%26nbsp%3B%3C%2FU%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20unfornate%20thing%20is%20that%20%3DNETWORKDAYS%20includes%20today's%20day%2C%20meaning%20they%20return%20the%20values%20as%20-5%2C%201%20and%202%20in%20the%20above%20examples.%20A%20simple%20fix%20would%20be%20something%20like%20''%3DNETWORKDAYS(A2%3B%20C2)-1''.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20the%20question%20is%3A%20How%20do%20I%20adjust%20the%20formula%20so%20that%20it%20adds%20''1%20day''%20if%20the%20value%20returned%20is%20a%20negative%20number%2C%20and%20substract%20''1%20day''%20if%20the%20value%20is%20returned%20as%20a%20positive%20number%3F%20I%20suppose%20it's%20with%20IF%20but%20I%20have%20no%20idea%20how%20to%20formulate%20the%20formula.%20Anybody%20can%20help%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3202888%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3202935%22%20slang%3D%22en-US%22%3ERe%3A%20%3DNETWORKDAYS%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3202935%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1313580%22%20target%3D%22_blank%22%3E%40PAtesting%3C%2FA%3E%26nbsp%3BI%20believe%20this%20one%20would%20work%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DNETWORKDAYS(A2%3BB2)%2BSIGN(A2-B2)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3203582%22%20slang%3D%22en-US%22%3ERe%3A%20%3DNETWORKDAYS%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3203582%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1313580%22%20target%3D%22_blank%22%3E%40PAtesting%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20would%20be%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DNETTO.WERKDAGEN(A2%3BB2)%2BPOS.NEG(A2-B2)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFormat%20the%20cell%20with%20the%20formula%20as%20General%20(Standaard)%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional 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 (Occasional 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!