Time card/sheet for semi-monthly pay period with overtime calculation

Clarence.Cox

New Member
Joined
Jun 16, 2010
Messages
6
Hello.

I'm working on a dynamic payroll spreadsheet that will automatically calculate the overtime worked in a week. Right now, I'm running into a snag. My issue is with the formula in Column R. Right now, as shown below, it is doing the calculation based on regular hours minus 40 to determine the OT time. The snag is very messy and it lay in this: while the row by row calculations for total overtime worked for the week is correct, the sum at the bottom is very much off. I need an accurate method to sum the hours of overtime for the given column.

Here are the guidelines for the pay periods and overtime:
1. The pay periods for the month go from the 1st to the 15th and the 16th to EOM (End of Month). This means that the pay period could end on any given day of the week. More on this in a moment.
2. A work week is defined as Sunday to Saturday.
3. Overtime is calculated based on the rule of anything over 40 hours in a given work week.
4. Holiday hours worked do not count towards the 40 hour mark in granting overtime since Holiday pay is automatically overtime.


If it were just a matter of a bi-weekly (every 2 weeks) pay period, I would simply state =IF(weekday(DATE)=7,Hours_Worked - 40,0), and tag a SUM(range) at the bottom. Unfortunately, with it being a semi-monthly (twice a month), the end of the pay period could be a Wednesday, so a reference to day of the week won't work unless the formula can dynamically determine which set of data to evaluate.


I've beat my head against the wall on how to figure this out for a good chunk of today. I'm completely willing to toss out the current method of determining overtime. :)



NOTE: This is the calculations sheet that references a cleanly formatted and designed time card on a tab called "Time Card", so this isn't the full workbook. In fact, once the whole thing is done, this calculation sheet will be hidden.

Columns M and N (which are formula referenced in Column P) are basic End - Start calculations and were hidden to simplify the display as well as the number of formulas displayed.

Column L (formula referenced) is a Yes/No display for if the date in question is holiday pay.



Excel 2003
HIOPQRS
1Start WorkTime OutDay CountDaily TotalHoursOT hoursHoliday
2Work
31:00:00 AM11:00:00 PM122.0022.0000
41:00:00 AM11:00:00 PM222.004440
51:00:00 AM10:00:00 PM321.0065250
61:00:00 AM12:00:00 AM423.0088480
71:00:00 AM12:00:00 AM523.00111710
812:00:00 AM12:00:00 AM60.00111710
912:00:00 AM12:00:00 AM70.00111710
1012:00:00 AM12:00:00 AM10.00000
192900

<tbody>
</tbody>
Sheet2

Worksheet Formulas
CellFormula
H3='Time Card'!C9
I3=IF(AND('Time Card'!D9=0,'Time Card'!F9<>0),'Time Card'!F9,'Time Card'!D9)
H4='Time Card'!C10
I4=IF(AND('Time Card'!D10=0,'Time Card'!F10<>0),'Time Card'!F10,'Time Card'!D10)
H5='Time Card'!C11
I5=IF(AND('Time Card'!D11=0,'Time Card'!F11<>0),'Time Card'!F11,'Time Card'!D11)
H6='Time Card'!C12
I6=IF(AND('Time Card'!D12=0,'Time Card'!F12<>0),'Time Card'!F12,'Time Card'!D12)
H7='Time Card'!C13
I7=IF(AND('Time Card'!D13=0,'Time Card'!F13<>0),'Time Card'!F13,'Time Card'!D13)
H8='Time Card'!C14
I8=IF(AND('Time Card'!D14=0,'Time Card'!F14<>0),'Time Card'!F14,'Time Card'!D14)
H9='Time Card'!C15
I9=IF(AND('Time Card'!D15=0,'Time Card'!F15<>0),'Time Card'!F15,'Time Card'!D15)
H10='Time Card'!C16
I10=IF(AND('Time Card'!D16=0,'Time Card'!F16<>0),'Time Card'!F16,'Time Card'!D16)
O3=IF('Time Card'!A9>=1,WEEKDAY('Time Card'!A9),0)
P3=M3+N3
Q3=P3
R3=IF(L3="No",IF(Q3>40,Q3-40,0),IF((Q3-P3)>40,Q3-40,0))
S3=IF(L3="Yes",P3,0)
O4=IF('Time Card'!A10>=1,WEEKDAY('Time Card'!A10),0)
P4=M4+N4
Q4=IF(Sheet2!O4>1,P4+Q3,P4)
R4=IF(L4="No",IF(Q4>40,Q4-40,0),IF((Q4-P4)>40,Q4-40,0))
S4=IF(L4="Yes",P4,0)
O5=IF('Time Card'!A11>=1,WEEKDAY('Time Card'!A11),0)
P5=M5+N5
Q5=IF(Sheet2!O5>1,P5+Q4,P5)
R5=IF(L5="No",IF(Q5>40,Q5-40,0),IF((Q5-P5)>40,Q5-40,0))
S5=IF(L5="Yes",P5,0)
O6=IF('Time Card'!A12>=1,WEEKDAY('Time Card'!A12),0)
P6=M6+N6
Q6=IF(Sheet2!O6>1,P6+Q5,P6)
R6=IF(L6="No",IF(Q6>40,Q6-40,0),IF((Q6-P6)>40,Q6-40,0))
S6=IF(L6="Yes",P6,0)
O7=IF('Time Card'!A13>=1,WEEKDAY('Time Card'!A13),0)
P7=M7+N7
Q7=IF(Sheet2!O7>1,P7+Q6,P7)
R7=IF(L7="No",IF(Q7>40,Q7-40,0),IF((Q7-P7)>40,Q7-40,0))
S7=IF(L7="Yes",P7,0)
O8=IF('Time Card'!A14>=1,WEEKDAY('Time Card'!A14),0)
P8=M8+N8
Q8=IF(Sheet2!O8>1,P8+Q7,P8)
R8=IF(L8="No",IF(Q8>40,Q8-40,0),IF((Q8-P8)>40,Q8-40,0))
S8=IF(L8="Yes",P8,0)
O9=IF('Time Card'!A15>=1,WEEKDAY('Time Card'!A15),0)
P9=M9+N9
Q9=IF(Sheet2!O9>1,P9+Q8,P9)
R9=IF(L9="No",IF(Q9>40,Q9-40,0),IF((Q9-P9)>40,Q9-40,0))
S9=IF(L9="Yes",P9,0)
O10=IF('Time Card'!A16>=1,WEEKDAY('Time Card'!A16),0)
P10=M10+N10
Q10=IF(Sheet2!O10>1,P10+Q9,P10)
R10=IF(L10="No",IF(Q10>40,Q10-40,0),IF((Q10-P10)>40,Q10-40,0))
S10=IF(L10="Yes",P10,0)
R19=IF(ISERROR(SUM(R3:R18)),SUM(R3:R17),SUM(R3:R18))
S19=SUM(S3:S18)

<tbody>
</tbody>

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Clarence.Cox

New Member
Joined
Jun 16, 2010
Messages
6
I figured it out. I'll post solution when I get home to use the HTML Maker to display the formulas.
 

Clarence.Cox

New Member
Joined
Jun 16, 2010
Messages
6
Now then... The issue I had been running into was that I was looking for an all-encompassing formula that I could auto-fill from the top to the bottom. What I eventually figured out (wow, did it take me way too long for that) was that there isn't such a formula, or - if there is - it's way too complicated for us to figure out.

I threw out my old calculations for regular time and overtime and then used a lookup for the last day of the week with a special case formula at the very end to catch the "short" week works fine.

Here is every possible combination of work days for a pay period:

Excel 2007
XYZAAABACAD
37654321
41765432
52176543
63217654
74321765
85432176
96543217
107654321
111765432
122176543
133217654
144321765
155432176
166543217
177654321
181765432

<tbody>
</tbody>
Sheet2



It doesn't matter what the starting date is, it will always be one of seven possible days of the week. Taking that into mind, I used an IF check for it the day of the week was Saturday, and summed the previous 7 days worth of staffed time. The special formula is for the short week (the one at the end). Additionally, the distance from the end of a week and the start of the pay period could be short, so in the first 7 rows, I had to shorten the SUM() range based on the longest possible start to end of week. At the 8th row down to the next-to-last row, the SUM() range will always be current row and the 6 previous rows.

For the very last row, since some pay periods were 15 days and others 16 days (and would therefore return #VALUE for a WEEKDAY() formula since I have excel blank the date on the payroll sheet since it is a different pay period), I instead used the starting day of the pay period, since a fixed distance after it would always be the same.


Excel 2007
OPQRSTU
1Day CountDaily WorkedHolidayWeekly HoursWeekly HolidayRegular HoursOT hours
2
360.00000.00000.00000.00000.00000.0000
479.44060.00009.44060.00009.44060.0000
519.09140.00000.00000.00000.00000.0000
628.61310.00000.00000.00000.00000.0000
730.00000.00000.00000.00000.00000.0000
840.00000.00000.00000.00000.00000.0000
950.00000.00000.00000.00000.00000.0000
1060.00000.00000.00000.00000.00000.0000
1170.00000.000017.70440.000017.70440.0000
1210.00000.00000.00000.00000.00000.0000
1320.00000.00000.00000.00000.00000.0000
1430.00000.00000.00000.00000.00000.0000
1540.00000.00000.00000.00000.00000.0000
1650.00000.00000.00000.00000.00000.0000
1760.00000.00000.00000.00000.00000.0000
18####0.00000.00000.00000.00000.00000.0000
190.000027.14500.0000

<tbody>
</tbody>
Sheet2

Worksheet Formulas
CellFormula
O3=IF('Time Card'!A9>=1,WEEKDAY('Time Card'!A9),0)
P3=M3+N3
Q3=IF(L3="Yes",P3,0)
R3=IF(O3=7,P3,0)
S3=IF(O3=7,Q3,0)
T3=IF((R3-S3)>40,40,R3-S3)
U3=IF((R3-S3)>40,R3-S3-40,0)
O4=IF('Time Card'!A10>=1,WEEKDAY('Time Card'!A10),0)
P4=M4+N4
Q4=IF(L4="Yes",P4,0)
R4=IF(O4=7,SUM(P3:P4),0)
S4=IF(O4=7,SUM(Q3:Q4),0)
T4=IF((R4-S4)>40,40,R4-S4)
U4=IF((R4-S4)>40,R4-S4-40,0)
O5=IF('Time Card'!A11>=1,WEEKDAY('Time Card'!A11),0)
P5=M5+N5
Q5=IF(L5="Yes",P5,0)
R5=IF(O5=7,SUM(P3:P5),0)
S5=IF(O5=7,SUM(Q3:Q5),0)
T5=IF((R5-S5)>40,40,R5-S5)
U5=IF((R5-S5)>40,R5-S5-40,0)
O6=IF('Time Card'!A12>=1,WEEKDAY('Time Card'!A12),0)
P6=M6+N6
Q6=IF(L6="Yes",P6,0)
R6=IF(O6=7,SUM(P3:P6),0)
S6=IF(O6=7,SUM(Q3:Q6),0)
T6=IF((R6-S6)>40,40,R6-S6)
U6=IF((R6-S6)>40,R6-S6-40,0)
O7=IF('Time Card'!A13>=1,WEEKDAY('Time Card'!A13),0)
P7=M7+N7
Q7=IF(L7="Yes",P7,0)
R7=IF(O7=7,SUM(P3:P7),0)
S7=IF(O7=7,SUM(Q3:Q7),0)
T7=IF((R7-S7)>40,40,R7-S7)
U7=IF((R7-S7)>40,R7-S7-40,0)
O8=IF('Time Card'!A14>=1,WEEKDAY('Time Card'!A14),0)
P8=M8+N8
Q8=IF(L8="Yes",P8,0)
R8=IF(O8=7,SUM(P3:P8),0)
S8=IF(O8=7,SUM(Q3:Q8),0)
T8=IF((R8-S8)>40,40,R8-S8)
U8=IF((R8-S8)>40,R8-S8-40,0)
O9=IF('Time Card'!A15>=1,WEEKDAY('Time Card'!A15),0)
P9=M9+N9
Q9=IF(L9="Yes",P9,0)
R9=IF(O9=7,SUM(P3:P9),0)
S9=IF(O9=7,SUM(Q3:Q9),0)
T9=IF((R9-S9)>40,40,R9-S9)
U9=IF((R9-S9)>40,R9-S9-40,0)
O10=IF('Time Card'!A16>=1,WEEKDAY('Time Card'!A16),0)
P10=M10+N10
Q10=IF(L10="Yes",P10,0)
R10=IF(O10=7,SUM(P4:P10),0)
S10=IF(O10=7,SUM(Q4:Q10),0)
T10=IF((R10-S10)>40,40,R10-S10)
U10=IF((R10-S10)>40,R10-S10-40,0)
O11=IF('Time Card'!A17>=1,WEEKDAY('Time Card'!A17),0)
P11=M11+N11
Q11=IF(L11="Yes",P11,0)
R11=IF(O11=7,SUM(P5:P11),0)
S11=IF(O11=7,SUM(Q5:Q11),0)
T11=IF((R11-S11)>40,40,R11-S11)
U11=IF((R11-S11)>40,R11-S11-40,0)
O12=IF('Time Card'!A18>=1,WEEKDAY('Time Card'!A18),0)
P12=M12+N12
Q12=IF(L12="Yes",P12,0)
R12=IF(O12=7,SUM(P6:P12),0)
S12=IF(O12=7,SUM(Q6:Q12),0)
T12=IF((R12-S12)>40,40,R12-S12)
U12=IF((R12-S12)>40,R12-S12-40,0)
O13=IF('Time Card'!A19>=1,WEEKDAY('Time Card'!A19),0)
P13=M13+N13
Q13=IF(L13="Yes",P13,0)
R13=IF(O13=7,SUM(P7:P13),0)
S13=IF(O13=7,SUM(Q7:Q13),0)
T13=IF((R13-S13)>40,40,R13-S13)
U13=IF((R13-S13)>40,R13-S13-40,0)
O14=IF('Time Card'!A20>=1,WEEKDAY('Time Card'!A20),0)
P14=M14+N14
Q14=IF(L14="Yes",P14,0)
R14=IF(O14=7,SUM(P8:P14),0)
S14=IF(O14=7,SUM(Q8:Q14),0)
T14=IF((R14-S14)>40,40,R14-S14)
U14=IF((R14-S14)>40,R14-S14-40,0)
O15=IF('Time Card'!A21>=1,WEEKDAY('Time Card'!A21),0)
P15=M15+N15
Q15=IF(L15="Yes",P15,0)
R15=IF(O15=7,SUM(P9:P15),0)
S15=IF(O15=7,SUM(Q9:Q15),0)
T15=IF((R15-S15)>40,40,R15-S15)
U15=IF((R15-S15)>40,R15-S15-40,0)
O16=IF('Time Card'!A22>=1,WEEKDAY('Time Card'!A22),0)
P16=M16+N16
Q16=IF(L16="Yes",P16,0)
R16=IF(O16=7,SUM(P10:P16),0)
S16=IF(O16=7,SUM(Q10:Q16),0)
T16=IF((R16-S16)>40,40,R16-S16)
U16=IF((R16-S16)>40,R16-S16-40,0)
O17=IF('Time Card'!A23>=1,WEEKDAY('Time Card'!A23),0)
P17=M17+N17
Q17=IF(L17="Yes",P17,0)
R17=IF(O17=7,SUM(P11:P17),0)
S17=IF(O17=7,SUM(Q11:Q17),0)
T17=IF((R17-S17)>40,40,R17-S17)
U17=IF((R17-S17)>40,R17-S17-40,0)
O18=IF('Time Card'!A24>=1,WEEKDAY('Time Card'!A24),0)
P18=M18+N18
Q18=IF(L18="Yes",P18,0)
R18=IF(O3=7,P18,IF(O3=1,SUM(P17:P18),IF(O3=2,SUM(P16:P18),IF(O3=3,SUM(P15:P18),IF(O3=4,SUM(P14:P18),IF(O3=5,SUM(P13:P18),IF(O3=6,SUM(P12:P18),0)))))))
S18=IF(O3=7,R18,IF(O3=1,SUM(Q17:Q18),IF(O3=2,SUM(Q16:Q18),IF(O3=3,SUM(Q15:Q18),IF(O3=4,SUM(Q14:Q18),IF(O3=5,SUM(Q13:Q18),IF(O3=6,SUM(Q12:Q18),0)))))))
T18=IF((R18-S18)>40,40,R18-S18)
U18=IF((R18-S18)>40,R18-S18-40,0)
S19=SUM(S3:S18)
T19=SUM(T3:T18)
U19=SUM(U3:U18)

<tbody>
</tbody>

<tbody>
</tbody>



The above formulas, in conjunction with basic End Time - Start Time calculations (to get the Daily Worked values), will get you a pretty nifty calculation sheet to slap onto a semi-monthly (twice monthly) payroll sheet.


Enjoy! :)
 

Watch MrExcel Video

Forum statistics

Threads
1,118,963
Messages
5,575,265
Members
412,653
Latest member
JyothiGrace
Top