Delivery Variance - using DATEDIF (+/-) and Networkdays

[janeyin]

Hi there

I have the following formula

IF(L4>I4,DATEDIF(I4,L4,"d"),-DATEDIF(L4,I4,"d"))))

I column is the due date and L column is the completed date

The formula is working to show completed task delivery (positive or negative) against due date

However, I would like to exclude weekends from the result. I am guessing I need to include networkdays somewhere but can't seem to get it to work

Help would be much appreciated!

Thanks so much!

 

[FormR]

Hi, don't you just need:

=NETWORKDAYS(I4,L4)

You might need to format the cell as general or number.

 

[janeyin]

Thanks for you help, that doesn't seem to work either though, I tried it with -1 as follows

NETWORKDAYS(I4,L4)-1)

Because if the task is completed on the due date I want the variance to be 0

Networkdays -1 all works ok until a task is completed early, in which case I wanted a negative variance e.g if task was due 6th September and delivered 5th September I wanted it to display -1

Datedif with the + and - works for the positive and negatives just can't get the working days only...........

 

[FormR]

Hi, how about this?

=NETWORKDAYS(I4,L4)+IF(I4>L4,1,-1)

 

[Tetra201]

Withdrawn

 

[janeyin]

Perfect - you are wonderful. Thank you so much

 

[FormR]

Happy to help, oh and welcome to the forum