Hi
I decided to write my first user defined function. I think it actually works.
Does not do holidays yet. Let me know if it works and I'll figure out how to add holidays as well.
Copy this code into a standard module or in your personal macro book.
Call it like a normal function.
=nhw(BeginningDateTime,EndingDateTime)
if the beginning date and time is in cell A1
ending date and time is in B1
then place:
= nhw(A1,B1)
in another cell
Will output "17 hrs. - 1 min."
Tested over a weekend as well.
Will not allow an invalid beginning or ending time to be entered. After 2 PM, before 6 am, or on a weekend day.
Let me know if it works.
Function nhw(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"
nhw = "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"
nhw = "Invalid Ending Date"
Exit Function
End If
If Hour(StartingTime)< 6 Or Hour(StartingTime) > 14 Then
MsgBox "Starting Time is invalid. Must be between 06:00 AM to 02:00 PM"
nhw = "Invalid Starting Time"
Exit Function
End If
If Hour(EndingTime)< 6 Or Hour(EndingTime) > 14 Then
MsgBox "Ending Time is invalid. Must be between 06:00 AM to 02:00 PM"
nhw = "Invalid Ending Time"
Exit Function
End If
If DateDiff("d", StartingTime, EndingTime) > 365 Then
MsgBox "Out of range - Greater than one year"
nhw = "Invalid Ending Time"
Exit Function
End If
ThisDayEnd = Month(StartingTime) & "/" & Day(StartingTime) & "/" & _
Year(StartingTime) & " " & "14:00:00 PM"
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) & " " & "06:00:00 AM"
ThisDayEnd = ThisDayBegin + #8:00:00 AM#
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:
nhw = Int(TotalNetHoursWorked / 60) & " hrs. - " & TotalNetHoursWorked Mod 60 & " min."
End Function
Tom
This message was edited by TsTom on 2002-04-09 09:27