Need help for hour calculation.
MZ Tools makes life easier for the Excel VBA coder
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Need help for hour calculation.

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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!!!!!

  2. #2
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,240
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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


    Keep Excelling.

    Damon

    VBAexpert Excel Consulting
    LinkedIn Profile http://www.linkedin.com/pub/damon-ostrander/7/79/a93
    AllExperts Profile http://www.allexperts.com/ep/1059-30...-Ostrander.htm

  3. #3
    New Member
    Join Date
    Mar 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,329
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default



    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

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,329
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

      

    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?


User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com