Need help for hour calculation.

[Sunblade50]

I have been asked to create a spreadsheet to track the time difference between when parts become avalible till they are measured. This sounded fairly easy until I tried finding a formula to calculate the difference using the following criteria: The hours can only include: Mon. - Thurs. 8:00am-10:00pm, 8:00am-7:00pm Fri. and no hours on the weekends. This is where I'm running into a wall. I tried using a chart which assigns either a 0 or 1 to each hour in a seven day cycle using day codes. I haven't had any luck with this. I didn't have any luck with logic functions or date functions either. If anyone can help, I would be eternally grateful!!!!!

[Damon Ostrander]

Hi Sunblade50,

I believe that the following UDF does what you want. It calculates the number of business hours between two dates where the start and end of business hours is defined for each day of the week in two arrays within the function:

Function WkgHrs(StartTime As Date, EndTime As Date) As Single
'This function calculates the number of working hours between
'two date-time values. Working hours are defined as Mon - Thurs,
'0800 - 2200 and Friday 0800-1900 hours. Fractions of hours are
'included in the calculations.
Dim Hstart As Variant 'Starting hour array
Dim Hend As Variant 'Ending hour array
Dim DOW As Integer 'Day of week (1=Sunday, 2=Monday, 3=Tuesday, etc.)
Dim DOWstart As Integer
Dim DOWend As Integer
Dim D As Date
Dim DeltaH As Single 'Hours to be subtracted
Dim Tend As Single
Dim Tstart As Single

Hstart = Array(0, 0, 8, 8, 8, 8, 8, 0)
Hend = Array(0, 0, 22, 22, 22, 22, 19, 0)

WkgHrs = 0

'First sum hour for whole days
For D = StartTime To EndTime
DOW = Weekday(D)
WkgHrs = WkgHrs + Hend(DOW) - Hstart(DOW)
Next D

'Now subtract time for partial days
DOW = Weekday(StartTime)
Tstart = 24 * (StartTime - Int(StartTime))
If Tstart > Hstart(DOW) And Hstart(DOW) <> 0 Then
WkgHrs = WkgHrs - (Tstart - Hstart(DOW))
End If

DOW = Weekday(EndTime)
Tend = 24 * (EndTime - Int(EndTime))
If Tend < Hend(DOW) And Hend(DOW) <> 24 Then
WkgHrs = WkgHrs - (Hend(DOW) - Tend)
End If

End Function

[Sunblade50]

Thanks Damon, I'll give it a try, I'm not real adapt at VBA but a fellow coworker is going to give me a hand with setting up the code on the worksheet. You have no idea how long I've worked on this trying to use formulas to no avail. I'll let you know how everything comes out.

[Dave Patton]

Could be done with a formula.
The array part is to calculate the number of Fridays.

=(WEEKDAY(A2,2)<5)*(HOUR(rS)-HOUR(A2))+(WEEKDAY(A2,2)=5)*(HOUR(rF)-HOUR(A2))+(NETWORKDAYS(A2,A3)-2)*14+HOUR(A3)-8+SUM(1*(WEEKDAY(ROW(INDIRECT(A5+1&":"&B5-1)))=6))*-3

rS is a regular day c/w time end of day
rF is a Friday c/w time time end of day

a2 Start c/w time
a3 Finish c/w time
a5 Start date
b5 Finish date

[Dave Patton]

The UDF and my formula give the same answer
with my 1 test.

N.B. Since the formula uses Networkdays,
the Analysis Toolpak must be installed.

Networkdays can also consider a range of Holidays.



Off topic

When I Submit a suggestion, screen becomes quite dark. What do I have to change?