# A function that separates hours worked in work shifts that overlap

1. ## 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. ## 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. ## 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

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

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