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
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(B1Cell C3 =IF(C1 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
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.
-------------------------
Hope this is helpful.
-------------------------
Have been away from the forum for quite a while, but am now back.
Like this thread? Share it with others