I am trying to calculate the # Days an order is Early or Late and am think I've figured out one way and am hoping for confirmation and also to find out if there is a better or easier way.
My data is set up:
Column D = Order Date
Column E = Due Date
Column F = Completed Date
I would like to exclude the Due Date from the count. i.e. If an order is received on the Due Date then the result should be "0". If it is received the day after the Due Date it should be "1" or if it is received the day before the Due Date it should be "-1".
I have a tab labeled Holidays with the dates in Column B. Using this formula:
=IF((NETWORKDAYS(E255,F255,Holidays!$B$3:$B$1000))<=0,(NETWORKDAYS(E255,F255,Holidays!$B$3:$B$1000)+1),(NETWORKDAYS(E255,F255,Holidays!$B$3:$B$1000)-1))
With the following data:
d2 = 02/04/13
e2 = 02/07/13
f2 = 02/07/13 (There are no holidays during the period 2/4/13 - 2/8/13.)
It seems to work properly. If f2 = 2/06/13 it returns "-1", if f2 = 2/07/13 it returns "0", and if f2 = 2/08/13 it returns "1".
Is there a better way to do this? I try to use the "KISS" rule (Keep It Simple Stupid) but this seems a bit convoluted.![]()
Any feedback is appreciated.
Bookmarks