Excel date/time computing

[sweethrtcc]

I need help converting a date and time to total hours on a job but if it spans past the normal work hours to only add actual work hours (not evening/weekend hours)
EX:
04/02/2002 09:41 AM
04/04/2002 10:42 AM
If normal work hours are 6:00 AM - 2:00 PM the total time should be 17 hr 1 min

[Dave Hawley]

Hi

Try:

=NETWORKDAYS($A$1,$A$2)*(8/24) and Custom format as "[h]:mm:ss"

This assumes the normal working hours are 8, eg 6am-2pm

[sweethrtcc]

I tried this but it only gives me the total days (in hours ie 16:00) instead of the time elapsed including the minutes

[Ian Mac]

Try:

="14:00"-TEXT(A1,"h:mm")+((NETWORKDAYS(A1,A2)-2)*"8:00")+(TEXT(A2,"h:mm")-"6:00")

format as [h]:mm

I haven't tested it over a weekend or holiday, just with what you gave, let me know if you need any more.

[Tom Schreiner]

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 ]

[sweethrtcc]

Thanks for the answers. They both worked great. I do, however, love the one with the weekends. If it would not be too much trouble I would love to have the holidays also figured. You mentioned that you could probably do so. If not, then thanks for all your help. It was greatly appreciated.

[Tom Schreiner]

Hi
Several of us offered to include holidays.
Which function did you use and what are your companies holidays?
Tom

[sweethrtcc]

TsTom,
I used yours. Holidays are Jan 1, Memorial day (this year May 27), July 4, Labor Day (this year Sep 2), Thanksgiving & day after (Nov 28, 29), Christmas (Dec 24-31).

Again, Thanks so much.

[Tom Schreiner]

Hi sweethrtcc.
I have 'till memorial day to get his figured out, right?
Any one want to risk a migraine and make my function work with holidays???
Dave Hawley! Do not make fun of Tom's first function! Remember This!
I can do in 100 lines, what you can do in two! Am tired. Later...

Will Post it here on May 6
Search for your username sweethrtcc
Or you can E-Mail me
TsTom@hotmail.com
Tom