Difference Between two dates

[andyjt1990]

Hi All,

I have a Target Date and a date when a job was sent to a customer.

Im trying to get a formula that calculates if a job was sent to a customer on time, or if there is a a positive / negative time variance.

I am using =NETWORKDAYS(D10,I10) which is giving me the right results most of the time. The problem i am having is if i send work on the day it is due it is coming up with a 1. E.g. =NETWORKDAYS(07/12/2017,17/12/2017).

Is it possible to change the formula slightly so if work is sent out on the day it is due to be the result is 0?

Thanks in advance.

[Special-K]

=NETWORKDAYS(start,end)-1 ?

What do you consider =NETWORKDAYS("07/12/2017","11/12/2017") should return ? 2 or 3 days ?

I suppose you could do this

IF(D10=I10,0,NETWORKDAYS(D10,I10))

[Zer0Cool]

Define "on-time". If its just that the difference between the day sent and due date is 0 or greater you dont need NETWORKDAYS. Just due date - send date. NETWORKDAYS is intended to calculate difference between dates in terms of work days between 2 dates (M-F, exclude optional holidays). I dont see any reason you need to ignore weekends in your calculations.

[andyjt1990]

Thanks for this. The formula IF(D10=I10,0,NETWORKDAYS(D10,I10)) is working how i want it to mostly.

Do you know how i could also put onto this formula to produce a blank cell if there is no data in D10?

[kersplash]

Thanks for this. The formula IF(D10=I10,0,NETWORKDAYS(D10,I10)) is working how i want it to mostly.

Do you know how i could also put onto this formula to produce a blank cell if there is no data in D10?

Just add an IF(D10="","",your formula)

[Special-K]

=if(d10="","",if(d10=i10,0,networkdays(d10,i10)))