+ Reply to Thread
Results 1 to 3 of 3

A function that separates hours worked in work shifts that overlap

  1. #1
    Katybug1964
    Guest

    A function that separates hours worked in work shifts that overlap

    We have temporary employees; sometimes an employee's hours will run over into
    the next shift. Based on noting the employee's hours and the set time of
    each shift (i.e., 1st Shift: 9:00 am - 5:00 pm, 2nd Shift: 5:00 pm - 12:00
    am), I need a function that will automatically sort out which hours are First
    Shift, which are
    Second Shift, and so forth. Furthermore, I want the function to provide the
    actual number of hours worked on that shift in a separate cell. That is,
    for a person who worked 3:00 pm - 9:00 pm: 1st Shift 2 hours, 2nd Shift, 4
    hours, etc.

    I'm thinking this will probably be a nested function, I haven't been able to
    locate a function that will allow me to do the intial calculation. Any
    suggestions?

    Thank you.

    --
    Katy Garrabrant-Hogan
    PC Training Coordinator

  2. #2
    Toppers
    Guest

    RE: A function that separates hours worked in work shifts that overlap

    Hi,
    A starter:

    For First shift hours use =CalculateShiftTimes(starttime, endtime,1)
    ForSecond shift hours use =CalculateShiftTimes(starttime, endtime,2)


    There is no check for valid hours i.e. start before 09:00 or finish after
    midnight .


    Function CalculateShiftTimes(sTime, eTime, rtn)
    fsTime = 0 ' First Shift Hours
    ssTime = 0 ' Second Shift hours
    sb = 0.70833333 ' Shift change time i.e 17:00 hours

    If eTime = 0 Then eTime = 1 ' Shift finishes at midnight

    If eTime <= sb Then ' Finishes before 17:00
    fsTime = eTime - sTime ' First shift hours
    Else ' Finishes in second shift
    fsTime = sb - sTime ' First Shift Hours
    ssTime = eTime - sb ' Second shift hours
    End If
    If rtn = 1 Then
    rtnVal = fsTime
    Else
    rtnVal = ssTime
    End If
    CalculateShiftTimes = rtnVal
    End Function


    HTH

    "Katybug1964" wrote:

    > We have temporary employees; sometimes an employee's hours will run over into
    > the next shift. Based on noting the employee's hours and the set time of
    > each shift (i.e., 1st Shift: 9:00 am - 5:00 pm, 2nd Shift: 5:00 pm - 12:00
    > am), I need a function that will automatically sort out which hours are First
    > Shift, which are
    > Second Shift, and so forth. Furthermore, I want the function to provide the
    > actual number of hours worked on that shift in a separate cell. That is,
    > for a person who worked 3:00 pm - 9:00 pm: 1st Shift 2 hours, 2nd Shift, 4
    > hours, etc.
    >
    > I'm thinking this will probably be a nested function, I haven't been able to
    > locate a function that will allow me to do the intial calculation. Any
    > suggestions?
    >
    > Thank you.
    >
    > --
    > Katy Garrabrant-Hogan
    > PC Training Coordinator


  3. #3
    Katybug1964
    Guest

    RE: A function that separates hours worked in work shifts that ove

    Thank you! It's very helpful. I haven't written a macro in a while so this
    will be good practice.

    --
    Katy Garrabrant-Hogan
    PC Training Coordinator


    "Toppers" wrote:

    > Hi,
    > A starter:
    >
    > For First shift hours use =CalculateShiftTimes(starttime, endtime,1)
    > ForSecond shift hours use =CalculateShiftTimes(starttime, endtime,2)
    >
    >
    > There is no check for valid hours i.e. start before 09:00 or finish after
    > midnight .
    >
    >
    > Function CalculateShiftTimes(sTime, eTime, rtn)
    > fsTime = 0 ' First Shift Hours
    > ssTime = 0 ' Second Shift hours
    > sb = 0.70833333 ' Shift change time i.e 17:00 hours
    >
    > If eTime = 0 Then eTime = 1 ' Shift finishes at midnight
    >
    > If eTime <= sb Then ' Finishes before 17:00
    > fsTime = eTime - sTime ' First shift hours
    > Else ' Finishes in second shift
    > fsTime = sb - sTime ' First Shift Hours
    > ssTime = eTime - sb ' Second shift hours
    > End If
    > If rtn = 1 Then
    > rtnVal = fsTime
    > Else
    > rtnVal = ssTime
    > End If
    > CalculateShiftTimes = rtnVal
    > End Function
    >
    >
    > HTH
    >
    > "Katybug1964" wrote:
    >
    > > We have temporary employees; sometimes an employee's hours will run over into
    > > the next shift. Based on noting the employee's hours and the set time of
    > > each shift (i.e., 1st Shift: 9:00 am - 5:00 pm, 2nd Shift: 5:00 pm - 12:00
    > > am), I need a function that will automatically sort out which hours are First
    > > Shift, which are
    > > Second Shift, and so forth. Furthermore, I want the function to provide the
    > > actual number of hours worked on that shift in a separate cell. That is,
    > > for a person who worked 3:00 pm - 9:00 pm: 1st Shift 2 hours, 2nd Shift, 4
    > > hours, etc.
    > >
    > > I'm thinking this will probably be a nested function, I haven't been able to
    > > locate a function that will allow me to do the intial calculation. Any
    > > suggestions?
    > >
    > > Thank you.
    > >
    > > --
    > > Katy Garrabrant-Hogan
    > > PC Training Coordinator


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1