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