Negative values in networkdays

[slsaagar]

Hi,

I am using the networkdays formula to calculate the number of networkdays between two dates as 'difference'. i want  difference-1, so am using formula like

networkDays(billed_date,shipping_date)-1,

The numbers are showing correctly as long as the difference is a positive number, but when it is a negative number all I get is -1, irrespective of the actual difference.

please.... help to get the correct  negative numbers

thanks..

[avinashelite]

As per my knowledge , Networking days function always give the positive result and if it has any negative values then it will show as 0 and since your using -1 it will show as -1, try like this

if(billed_date>shipping_date,networkingdays(shipping_date,billed_date),'-&(networkingdays(billed_date,shipping_date)-1))

[giakoum]

this is how the function works.

For example the following returns 0 :

networkdays ('2007-02-19', '2007-01-01')

Try an If statement : if function returns zero then calculate the same but with the dates in different order :

if(networkdays ('2007-02-19', '2007-01-01') = 0, networkdays ('2007-01-01', '2007-02-19') * -1, networkdays ('2007-02-19', '2007-01-01'))

[Anonymous]

The first parameter to NetworkDays() is the start date and the second parameter is the end date.

If the the end date supplied is before the start date, then this is by definition invalid as something cannot end before it starts and a value of zero is returned.

So try using something like this :

= if ( billed_date> shipping_date,

( NetWorkDays ( shipping_date, billed_date) * -1 ) + 1 ,

NetWorkDays ( billed_date, shipping_date)  -1  )

[slsaagar]

thank u all for ur replies this formula worked for my creteria...

if(billeddate<=shippingdate,

NetWorkDays(billeddate,shippingdate)-1,(NetWorkDays(shippingdate,billeddate)*-1)-1)

[Anonymous]

sagar sl wrote:

thank u all for ur replies this formula worked for my creteria...

if(billeddate<=shippingdate,

NetWorkDays(billeddate,shippingdate)-1,(NetWorkDays(shippingdate,billeddate)*-1)-1)

Well, this is still wrong.
Let's say billeddate = 3/4/2016

              shippingdate = 4/4/2016

So, expression would be

=if(date(3/4/2016)<=date(4/4/2016),

NetWorkDays(date(3/4/2016),date(4/4/2016))-1,(NetWorkDays(date(4/4/2016),date(3/4/2016))*-1)-1)

And the result would still be -1

It can be simply done like this

If(NetWorkDays(billeddate ,shippingdate )=0,NetWorkDays(billeddate ,shippingdate ),NetWorkDays(billeddate ,shippingdate )-1)

Assumption : shippingdate >= billeddate

If that's not the case,
It should be

If(NetWorkDays(billeddate ,shippingdate )=0,If(NetWorkDays(shippingdate ,billeddate )=0,0,NetWorkDays(shippingdate ,billeddate )-1),NetWorkDays(billeddate ,shippingdate )-1)