# Employee payroll hours question

I'm trying to figure out ho to figure out when overtime hours start.

A couple of rules:  First of all, overtime is paid after 40 hours.   There is a 1st shift, a 2nd shift, and a 3rd shift.

Then imagine that an employee worked 8 hours on Monday on the 1st shift, 8 hours on Tuesday on the 1st shift, 8 hours on Wednesday on the 1st shift.  Then on Thursday he worked 8 hours on the 2nd shift.  Then on Friday he worked 16 hours on the 3rd shift.  So he worked a total of 48 hours.  I have to show the data like this and can't figure out how to do it.  I can easily show the data in the columns named "1st Shift", "2nd Shift", "3rd Shift", and "Total Hours" but I cannot figure out how to get the data in the columns named "1st Shift OT", "2nd Shift OT", and "3rd Shift OT".

--Steve
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
The following will calculate the values for you. You will need to translate them into whatever language you are using but you can achieve what you need with 3 IF statements.

``````if (1stShift > 40)
{
1stShiftOT = 1stShift - 40;
}
else
{
1stShiftOT = 0;
}

if (1stShiftOT > 0)
{
2ndShiftOT = 2ndShift;
}
else if (1stShift + 2ndShift > 40)
{
2ndShiftOT = (1stShift + 2ndShift) - 40;
}

if (2ndShiftOT > 0)
{
3rdShiftOT = 3rdShift;
}
else if (1stShift + 2ndShift + 3rdShift > 40)
{
3rdShiftOT = (1stShift + 2ndShift + 3rdShift) - 40;
}
``````

Hope this helps
0
Accountant/DeveloperCommented:
Here's to logic I would use ... The exact code would be determined by the method you are using (query or vba function).  Normally OT is not calculate by shift ... It is calculated over the 40 hour pay period.

But ... Let's start here ...

[OT] = IIf([1st Shift]  + [2nd Shift] + [3rd Shift] > 40, [1st Shift] + [2nd Shift] + [3rd Shift] - 40, 0)

ET
0
Commented:
but, before we jump into anything, post the table structure you are using to record the hours worked.
0
Commented:
If your using IIF statements then you should be able to do this for each field:

1stShiftOT = IIF(1stShift > 40, 1stShift - 40, 0)

2ndShiftOT = IIF(1stShiftOT > 0, 2ndShift, IIF(1stShift + 2ndShift > 40, (1stShift + 2ndShift) - 40, 0)

3rdShiftOT =  IIF(2ndShiftOT > 0, 3rdShift, IIF(1stShift + 2ndShift + 3rdShift > 40, (1stShift + 2ndShift + 3rdShift) - 40, 0)
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Commented:
I like the response from @etsherman.

Let's look at a different route though as well because I am not REALLY sure what you are looking for...

``````1st Shift OT: IIf([1st Shift] > 40,[1st Shift] - 40,0)
2nd Shift OT: IIf(([1st Shift] + [2nd Shift]) > 40,([1st Shift] + [2nd Shift]) - 40, 0)
3rd Shift OT: IIf(([1st Shift] + [2nd Shift] + [3rd Shift]) > 40,([1st Shift] + [2nd Shift] + [3rd Shift]) - 40, 0)
``````

@capricorn1 responded with table structure as I was writing this.  Table structure is needed for further analysis.

Additionally, how does day of the week play in to this?  At all?
0
Commented:
@Tony Taylor, in your example if the person enters overtime in shift 2 then the 3rd shift will over record the overtime.

1stShift = 22, 2ndShift = 21, 3rdShift = 20 would result in:

1stShiftOT = 0, 2ndShiftOT = 3, 3rdShiftOT = 23

Total OT would be 26 hours when it should be

1stShiftOT = 0, 2ndShiftOT = 3, 3rdShiftOT = 20 representing a total of 23 hours overtime.

You need to look at the previous shiftOT fields to identify if your in overtime or not as shown in my IIF and IF example.

Cant imaging that the 1st Shift would be 20+ hours but need to cater for any eventuality.
0
Commented:
Touche @NealKing, you are correct.

You posted while I was writing mine.
0
Author Commented:
Here is what I have so far.

Table structure:

Then I changed these lines:

1st Shift OT: IIf([1st Shift] > 40,[1st Shift] - 40,0)
2nd Shift OT: IIf(([1st Shift] + [2nd Shift]) > 40,([1st Shift] + [2nd Shift]) - 40, 0)
3rd Shift OT: IIf(([1st Shift] + [2nd Shift] + [3rd Shift]) > 40,([1st Shift] + [2nd Shift] + [3rd Shift]) - 40, 0)

To: (to deal with null values)

1st Shift OT: IIf(Nz([1st Shift Hours],0)>40,Nz([1st Shift Hours],0)-40,0)
2nd Shift OT: IIf((Nz([1st Shift Hours],0)+Nz([2nd Shift Hours],0))>40,(Nz([1st Shift Hours],0)+Nz([2nd Shift Hours],0))-40,0)
3rd Shift OT: IIf((Nz([1st Shift Hours],0)+Nz([2nd Shift Hours],0)+Nz([3rd Shift Hours],0))>40,(Nz([1st Shift Hours],0)+Nz([2nd Shift Hours],0)+Nz([3rd Shift Hours],0))-40,0)

But I think I'm missing something.

Using the data in the table like this:

The result in the query is:

And the I run the query it looks like:

The overtime hours should be reflected in the 2nd shift OT, not 3rd shift OT.  Right?
0
Commented:
is that the complete Table structure? don't you think you are missing some field?
0
Author Commented:
If I am I guess I don't know what it is.  Sorry.
0
Commented:
you are showing this in your query
1st Shift OT: IIf(Nz([1st Shift Hours],0)>40,Nz([1st Shift Hours],0)-40,0)

is the field [1st Shift Hours] in the table?
0
Commented:
It's not necessarily "missed fields"... It might just be missed calculations...

How is [1st Shift] calculated?  Is it a query that it is referring to?
0
Author Commented:
There are three "nested" queries in the query.  Here is the SQL for each of them if that helps:

1st Shift:
``````SELECT [Hospice Employee Timesheet].Employee, Sum([Hospice Employee Timesheet].[Service Time (Hours)]) AS [1st Shift Hours]
FROM [Hospice Employee Timesheet] LEFT JOIN tblActiveServiceCodes ON [Hospice Employee Timesheet].[Service Code] = tblActiveServiceCodes.[Service Code]
WHERE (((tblActiveServiceCodes.[Service Code]) Not Like "SN-2*")) OR (((tblActiveServiceCodes.[Service Code]) Not Like "SN-3*")) OR (((tblActiveServiceCodes.[Service Code]) Not Like "*On-Call")) OR (((tblActiveServiceCodes.[Service Code]) Not Like "Sick")) OR (((tblActiveServiceCodes.[Service Code]) Not Like "Vacation")) OR (((tblActiveServiceCodes.[Service Code]) Not Like "Holiday")) OR (((tblActiveServiceCodes.[Service Code]) Not Like "Personal")) OR (((tblActiveServiceCodes.[Service Code]) Not Like "SC Day Shift")) OR (((tblActiveServiceCodes.[Service Code]) Not Like "SC Evening Shift")) OR (((tblActiveServiceCodes.[Service Code]) Not Like "SC Night Shift")) OR (((tblActiveServiceCodes.[Service Code]) Not Like "*vol.-*")) OR (((tblActiveServiceCodes.[Service Code]) Not Like "volunteer visit"))
GROUP BY [Hospice Employee Timesheet].Employee;
``````

2nd Shift:
``````SELECT [Hospice Employee Timesheet].Employee, Sum([Hospice Employee Timesheet].[Service Time (Hours)]) AS [2nd Shift Hours]
FROM [Hospice Employee Timesheet]
WHERE ((([Hospice Employee Timesheet].[Service Code]) Like "SN-2*" And ([Hospice Employee Timesheet].[Service Code]) Not Like "*vol.-*" And ([Hospice Employee Timesheet].[Service Code]) Not Like "volunteer visit"))
GROUP BY [Hospice Employee Timesheet].Employee;
``````

3rd Shift:
``````SELECT [Hospice Employee Timesheet].Employee, Sum([Hospice Employee Timesheet].[Service Time (Hours)]) AS [3rd Shift Hours]
FROM [Hospice Employee Timesheet]
WHERE ((([Hospice Employee Timesheet].[Service Code]) Like 'SN-3*' And ([Hospice Employee Timesheet].[Service Code]) Not Like "*vol.-*" And ([Hospice Employee Timesheet].[Service Code]) Not Like "volunteer visit"))
GROUP BY [Hospice Employee Timesheet].Employee;
``````
0
Commented:
Please look at my posts as you are not using the overtime fields to identify if your in overtime within each band. However, based on the rules you gave your second example is correct.

1stShift = 24
2ndShift = 16 which gives 40 hours in total thus far.
3rdShift = 8 which is then in overtime so 8 hours 3rdShiftOT.

Your rules said that over 40 hours was treated as overtime.
0
Commented:
At this point I am fairly certain Mr Knackered (@NealKing) answered this question in post #41221371.

Now it sounds like a data issue.  If you want to open a new question and give us a sanitized version of the data, then we can look at it.

Without having access to the service code and without seeing the other table, it is VERY difficult to determine how the data is linking and why it shows the results that it does.

The original question was in reference to OT display.  In my opinion, that has been answered.
1
Author Commented:
Mr Knackered:  I am getting syntax error on this like:  (note the brackets because I need them, right?

2ndShiftOT: IIF([1stShiftOT]>0,[2nd Shift],IIF([1st Shift]+[2nd Shift]>40,([1st Shift]+[2nd Shift])-40,0))
0
Commented:
Try removing the brackets around ([1st Shift] + [2nd Shift]) to see if it's getting confused with the brackets. So just [1st Shift] + [2nd Shift] - 40,0)) at the end.
0
Author Commented:
I got it with...

2ndShiftOT: IIf([1stShiftOT]>0,Nz([2nd Shift Hours],0),IIf(Nz([1st Shift Hours],0)+Nz([2nd Shift Hours],0)>40,(Nz([1st Shift Hours],0)+Nz([2nd Shift Hours],0))-40,0))

(nz's added to account for null values).

I guess I'll award this question but I don't even know how to ask the next topic,  So confusing.
0
Commented:
@SteveL13: Might I suggest that you attach a sanitized version of the database, and ask the question as you wonderfully articulated in post 41228613.
0
Author Commented:
I will try.  But done for the day.  Have to leave.  Back at it tomorrow,
0
Commented:
Make sure you verify the OT rules.  My understanding of the FLSA is that OT is after 8 hours in a day or 40 hours in a week.  Union contracts might also impact the rules to make them more favorable to the employee.
0
Commented:
@SteveL13 the next thing to do is to sort out removing all your Nz(field, 0) bits from the IIF statements. You should, in my opinion always implement this ISNULL logic at a query level rather than via the IIF statement.

Example below of your 1st Shift Hours query but please do the same for your other two queries and then remove the load of Nz(field, 0) bits from your IIF statements.

``````SELECT [Hospice Employee Timesheet].Employee, Sum(ISNULL([Hospice Employee Timesheet].[Service Time (Hours)], 0)) AS [1st Shift Hours]
FROM [Hospice Employee Timesheet] LEFT JOIN tblActiveServiceCodes ON [Hospice Employee Timesheet].[Service Code] = tblActiveServiceCodes.[Service Code]
WHERE (((tblActiveServiceCodes.[Service Code]) Not Like "SN-2*")) OR (((tblActiveServiceCodes.[Service Code]) Not Like "SN-3*")) OR (((tblActiveServiceCodes.[Service Code]) Not Like "*On-Call")) OR (((tblActiveServiceCodes.[Service Code]) Not Like "Sick")) OR (((tblActiveServiceCodes.[Service Code]) Not Like "Vacation")) OR (((tblActiveServiceCodes.[Service Code]) Not Like "Holiday")) OR (((tblActiveServiceCodes.[Service Code]) Not Like "Personal")) OR (((tblActiveServiceCodes.[Service Code]) Not Like "SC Day Shift")) OR (((tblActiveServiceCodes.[Service Code]) Not Like "SC Evening Shift")) OR (((tblActiveServiceCodes.[Service Code]) Not Like "SC Night Shift")) OR (((tblActiveServiceCodes.[Service Code]) Not Like "*vol.-*")) OR (((tblActiveServiceCodes.[Service Code]) Not Like "volunteer visit"))
GROUP BY [Hospice Employee Timesheet].Employee;
``````

Sum(ISNULL([Hospice Employee Timesheet].[Service Time (Hours)], 0)) AS [1st Shift Hours]

Once you have done this and re-run your reports / query please create a new Question with the exact issue you think you still facing.
1
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.