Shipping Times

clogteachr

New Member
Joined
Jan 13, 2002
Messages
4
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
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
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.
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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 <reaches for his Mt.Dew for a caffeine suppliment>

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
 
Upvote 0

Forum statistics

Threads
1,213,514
Messages
6,114,078
Members
448,547
Latest member
arndtea

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top