Error Checking in Excel
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
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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
    Location
    England
    Posts
    5,552
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Calculating Shipping time EXCLUDING weekends

    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 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
  •