Need help for hour calculation.

Sunblade50

New Member
Joined
Mar 16, 2002
Messages
3
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!!!!!
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,539
Latest member
alex78

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