Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: Calculating Shipping time EXCLUDING weekends

  1. #1
    New Member
    Join Date
    Jan 2002
    Post Thanks / Like
    0 Post(s)
    0 Thread(s)


    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

  2. #2
    Board Regular
    Join Date
    Aug 2003
    Post Thanks / Like
    3 Post(s)
    1 Thread(s)

    Default Re: Calculating Shipping time EXCLUDING weekends


    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 Cell 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.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts