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

C

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>
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Sort by date Sort by votes
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! :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
Spreadsheet to calculate overtime and total hours
Replies
1
Views
675
HyraxWithAFlamethrower
H
C
  • Question
Calculate and Identify Ordinary Hours, Double time or 1.5 Time
Replies
0
Views
60
ChetanPuri
C
C
  • Question
Overtime Calculation
Replies
2
Views
164
ChetanPuri
C
R
  • Question
Payroll Macros
Replies
1
Views
557
rdifu
R
HappyChappy
  • Question
Timesheet Very Basic needs an expert eye
Replies
1
Views
196
HappyChappy
HappyChappy

Forum statistics

Threads
1,214,601
Messages
6,120,465
Members
448,965
Latest member
grijken

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top