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

Thread: Shipping Times

  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
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,426
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default


    look at NetWorkdays

    Returns the number of whole working days between start_date and end_date. Working days exclude weekends and any dates identified in holidays. Use NETWORKDAYS to calculate employee benefits that accrue based on the number of days worked during a specific term.
    If this function is not available, run the Setup program to install the Analysis ToolPak. After you install the Analysis ToolPak, you must enable it by using the Add-Ins command on the Tools menu.

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Southfield,MI USA
    Posts
    2,308
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try the function: NETWORKDAYS

    That will return the number of workdays (excludes weekends) between 2 specified dates. You also can add holidays into the formula - the help file should carry you through it. If you'd like, you can divide the entire formula by 86000 and format the cell as "hh:mm:ss" to get the requested formatting detail.

    Hope that helps
    Adam

  4. #4
    New Member
    Join Date
    Jan 2002
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    4/16/02 10:00:00 AM 4/16/02 1:00:00 PM 00:00:00

    I'm using the formula:
    =(NETWORKDAYS(A2,B2))/86000 and I get the 00:00:00 for the anser to the above dates/times.

    What is wrong?

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi
    I wrote a similiar function for net hours worked for someone else.
    Modified it to fit in your case.
    Place this code in a standard module.
    funtion nts - Net Shipping Time
    Call it from anywhere in your workbook as such:
    Syntax = nst(BeginningDateTime,EndingDateTime)
    If shipping date & time were in A2
    If recieving date & time were in B2

    =nst(A2,B2)

    Will display output in hours and minutes.
    Does not account for holidays yet.
    Let me know if it works for you.
    Thanks,
    Tom


    Option Explicit
    'Function to return the actual shipping time excluding weekends
    'holidays are unaccounted for
    Function nst(StartingTime As Date, EndingTime As Date)
    Dim TotalNetHoursWorked
    Dim ThisDayEnd As Date
    Dim ThisDayBegin As Date
    Dim Cntr As Integer

    If StartingTime = 0 Then Exit Function
    If EndingTime = 0 Then Exit Function
    If Format(StartingTime, "DDDD") = "Saturday" Or _
    Format(StartingTime, "DDDD") = "Sunday" Then
    MsgBox "Starting Date on " & Format(StartingTime, "DDDD") & " is Invalid"
    nst = "Invalid Starting Date"
    Exit Function
    End If
    If Format(EndingTime, "DDDD") = "Saturday" Or _
    Format(EndingTime, "DDDD") = "Sunday" Then
    MsgBox "Ending Date on " & Format(EndingTime, "DDDD") & " is Invalid"
    nst = "Invalid Ending Date"
    Exit Function
    End If
    If DateDiff("d", StartingTime, EndingTime) > 365 Then
    MsgBox "Out of range - Greater than one year"
    nst = "Invalid Ending Time"
    Exit Function
    End If
    ThisDayEnd = StartingTime + 1
    If ThisDayEnd > EndingTime Then
    TotalNetHoursWorked = DateDiff("n", StartingTime, EndingTime)
    GoTo AllDone
    End If

    TotalNetHoursWorked = DateDiff("n", StartingTime, ThisDayEnd)
    For Cntr = 1 To 365
    ThisDayBegin = Month(StartingTime + Cntr) & "/" & Day(StartingTime + Cntr) & "/" & _
    Year(StartingTime + Cntr)
    ThisDayEnd = ThisDayBegin + 1
    If ThisDayBegin > EndingTime Then Exit For
    If ThisDayEnd > EndingTime Then
    If Format(ThisDayEnd, "MMMM") = "Saturday" Then Exit For
    TotalNetHoursWorked = TotalNetHoursWorked + DateDiff("n", ThisDayBegin, EndingTime)
    Exit For
    End If
    If Format(ThisDayEnd, "DDDD") <> "Saturday" And _
    Format(ThisDayEnd, "DDDD") <> "Sunday" Then _
    TotalNetHoursWorked = TotalNetHoursWorked + DateDiff("n", ThisDayBegin, ThisDayEnd)
    Next
    AllDone:
    nst = Int(TotalNetHoursWorked / 60) & " hrs. - " & Round(TotalNetHoursWorked Mod 60) & " min."
    End Function


    [ This Message was edited by: TsTom on 2002-04-16 12:13 ]

  6. #6
    New Member
    Join Date
    Jan 2002
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    TomTS:
    Can you explain where to place this formula.

  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Denver, CO
    Posts
    1,743
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    if you'd prefer formulas over vba for this, you could use the ugly:

    =IF(NETWORKDAYS(A2,B2)=1,B2-A2,IF(NETWORKDAYS(A2,B2)=2,(1-(A2-INT(A2))+B2-INT(B2)),(NETWORKDAYS(A2,B2)-2)+(1-(A2-INT(A2))+B2-INT(B2))))

    apply custom format of [h]:mm to the cell you put this in...

    good luck

    [ This Message was edited by: IML on 2002-04-16 12:53 ]

  8. #8
    Board Regular
    Join Date
    Feb 2002
    Location
    Southfield,MI USA
    Posts
    2,308
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hey again,

    What the heck was I thinking? Ignore that junk I wrote about dividing by 86400 - I frequently convert a time given in #seconds to a time format. My brain must have just short-circuited

    BTW, Networkdays outputs whole days for some odd reason (rounded up - that's what I get for not actually trying my own advice). If you still want to go with a formula approach- try something like this:

    =NETWORKDAYS(StartDate,EndDate)-(1-((EndDate-StartDate)-ROUNDDOWN(EndDate-StartDate,0)))

    Format this as [h]:mm:ss

    Should do the trick. Sorry about the earlier post-

    Adam

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
  •