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".

Employee-Hours-Example.bmp
--Steve
SteveL13Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Mr KnackeredCommented:
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;
}

Open in new window


Hope this helps
Eric ShermanAccountant/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
Rey Obrero (Capricorn1)Commented:
but, before we jump into anything, post the table structure you are using to record the hours worked.
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Mr KnackeredCommented:
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)

Experts Exchange Solution brought to you by

Your issues matter to us.

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

Start your 7-day free trial
TONY TAYLORCommented:
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...

How about this:

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)

Open in new window


@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?
Mr KnackeredCommented:
@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.
TONY TAYLORCommented:
Touche @NealKing, you are correct.

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

Table structure:

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:

Data Example 1
The result in the query is:

Query Example 1
BUT, if the data was this:  

Data Examlpe 2
And the I run the query it looks like:

Query Example 2
The overtime hours should be reflected in the 2nd shift OT, not 3rd shift OT.  Right?
Rey Obrero (Capricorn1)Commented:
is that the complete Table structure? don't you think you are missing some field?
SteveL13Author Commented:
If I am I guess I don't know what it is.  Sorry.
Rey Obrero (Capricorn1)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?
TONY TAYLORCommented:
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?
SteveL13Author 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;

Open in new window


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;

Open in new window


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;

Open in new window

Mr KnackeredCommented:
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.
TONY TAYLORCommented:
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.
SteveL13Author 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))
Mr KnackeredCommented:
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.
SteveL13Author 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.
TONY TAYLORCommented:
@SteveL13: Might I suggest that you attach a sanitized version of the database, and ask the question as you wonderfully articulated in post 41228613.
SteveL13Author Commented:
I will try.  But done for the day.  Have to leave.  Back at it tomorrow,
PatHartmanCommented:
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.
Mr KnackeredCommented:
@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;

Open in new window


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