SOLVED

=NETWORKDAYS formula?

%3CLINGO-SUB%20id%3D%22lingo-sub-3203522%22%20slang%3D%22en-US%22%3E%3DNETWORKDAYS%20formula%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3203522%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20people%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20do%20I%20use%20the%20%3CSTRONG%3E%3DNETWORKDAYS%3C%2FSTRONG%3E%20and%20%3CSTRONG%3E%3DIF%3C%2FSTRONG%3E%20formula%20together%20to%20add%2Fsubstract%20''1%20day''%20when%20the%20returned%20value%20is%20either%20%26lt%3B0%20or%20%26gt%3B0%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20example%3A%3C%2FP%3E%3CP%3EIf%20returned%20value%20from%20%3CSTRONG%3E%3DNETWORKDAYS%3C%2FSTRONG%3E%20is%20-1%2C%20I%20want%20to%20display%20the%20value%20as%200%20(%3CEM%3EI%20want%20to%26nbsp%3B%3C%2FEM%3E%3CEM%3Eadd%20''1%20day''%20if%20%26lt%3B0)%3C%2FEM%3E%3C%2FP%3E%3CP%3EIf%20returned%20value%20from%20%3CSTRONG%3E%3DNETWORKDAYS%3C%2FSTRONG%3E%20is%204%2C%20I%20want%20to%20display%20the%20value%20as%203%20(%3CEM%3EI%20want%20to%26nbsp%3B%3C%2FEM%3E%3CEM%3Esubstract%20''1%20day''%20if%20%26gt%3B0)%3C%2FEM%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3203522%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-3203568%22%20slang%3D%22en-US%22%3ERe%3A%20%3DNETWORKDAYS%20formula%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3203568%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%3EWith%20dates%20in%20A1%20and%20B1%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3D(ABS(NETWORKDAYS(A1%2CB1))-1)*SIGN(NETWORKDAYS(A1%2CB1))%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hello people,

 

How do I use the =NETWORKDAYS and =IF formula together to add/substract ''1 day'' when the returned value is either <0 or >0?

 

For example:

If returned value from =NETWORKDAYS is -1, I want to display the value as 0 (I want to add ''1 day'' if <0)

If returned value from =NETWORKDAYS is 4, I want to display the value as 3 (I want to substract ''1 day'' if >0)

2 Replies
best response confirmed by PAtesting (Occasional Contributor)
Solution

@PAtesting 

With dates in A1 and B1:

 

=(ABS(NETWORKDAYS(A1,B1))-1)*SIGN(NETWORKDAYS(A1,B1))

 

In Dutch:

 

=(ABS(NETTO.WERKDAGEN(A1;B1))-1)*POS.NEG(NETTO.WERKDAGEN(A1;B1))

Hartelijk bedankt Hans!