Calculating Shipping time EXCLUDING weekends

[clogteachr]

Mr. Excel,
I've been asked by a friend to help with the automatic calculation of shipping times. His supervisor wants to know the length of time it takes to from date, hour & minute a package was shipped to the date, hour & minute it was delivered EXCLUDING weekends.

How can this be done? I've tried a couple of things, but cannot get the weekends out of it.


Sam Gill CPS

 

[onlyadrafter]

Hello,

I know you posted this a long time ago, but I have only recently registered, and am trying to work my way through the unanswered posts, to gain as much experience as possible. I don't know if you need this any more, but I think I have come up with a solution.

Cell A1 is the Shipping Date (e.g. 01/01/03 00:00)
Cell A2 is the Arrvival Date (e.g. 10/01/02 00:00)

Cell B1 = YEAR(A1)
Cell C1 = MONTH(A1)
Cell D1 = DAY(A1)
Cell E1 = HOUR(A1)
Cell F1 = MINUTE(A1)

Copy these cell down into B2:F2

Cell H2 = NETWORKDAYS(A1,A2)

Cell B3 =IF(B1<B2,B2-B1,B1-B2)
Cell C3 =IF(C1<C2,C2-C1,C1-C2)
Cell D3 =IF(D1<=D2,D2-D1,D1-D2)
Cell E3 =IF(E1<=E2,E2-E1,24-(E1-E2))
Cell F3 =IF(F1<=F2,F2-F1,60-(F1-F2))

Cell C5 'TOTAL YEARS'
Cell D5 =B3

Cell C6 'TOTAL MONTHS'
Cell D6 =C3

Cell C7 'TOTAL DAYS'
Cell D7 =IF(AND(E2<E1,D3=1),0,D3-(D3-H2)-1)

Cell C8 'TOTAL HOURS'
Cell D8 =E3

Cell C9 'TOTAL MINS'
Cell D9 =F3


I think this works correctly, I have tried a few different dates, and they do seem to work, but let me know how you get on, if you sill require this.

Hope this is helpful.