Excel date/time computing

sweethrtcc

New Member
Joined
Apr 4, 2002
Messages
8
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
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
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
 
Upvote 0
I tried this but it only gives me the total days (in hours ie 16:00) instead of the time elapsed including the minutes
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
Hi
Several of us offered to include holidays.
Which function did you use and what are your companies holidays?
Tom
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,184
Members
448,554
Latest member
Gleisner2

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