Shipping Times

[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

[Dave Patton]

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.

[Asala42]

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

[clogteachr]

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?

[Tom Schreiner]

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 ]

[clogteachr]

TomTS:
Can you explain where to place this formula.

[IML]

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 ]

[Asala42]

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