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

[Clarence.Cox]

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

	H	I	O	P	Q	R	S

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFFF99, align: center"]Start Work[/TD]
[TD="bgcolor: #FFFF99, align: center"]Time Out[/TD]
[TD="bgcolor: #FFFF99, align: center"]Day Count[/TD]
[TD="bgcolor: #FFFF99, align: center"]Daily Total[/TD]
[TD="bgcolor: #FFFF99, align: center"]Hours[/TD]
[TD="bgcolor: #FFFF99, align: center"]OT hours[/TD]
[TD="bgcolor: #FFFF99, align: center"]Holiday[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FFFF99, align: center"]Work[/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]1:00:00 AM[/TD]
[TD="align: center"]11:00:00 PM[/TD]
[TD="bgcolor: #C0C0C0, align: right"]1[/TD]
[TD="align: right"]22.00[/TD]
[TD="bgcolor: #FFFF99, align: center"]22.00[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]1:00:00 AM[/TD]
[TD="align: center"]11:00:00 PM[/TD]
[TD="bgcolor: #C0C0C0, align: right"]2[/TD]
[TD="align: right"]22.00[/TD]
[TD="bgcolor: #FFFF99, align: center"]44[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]1:00:00 AM[/TD]
[TD="align: center"]10:00:00 PM[/TD]
[TD="bgcolor: #C0C0C0, align: right"]3[/TD]
[TD="align: right"]21.00[/TD]
[TD="bgcolor: #FFFF99, align: center"]65[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]1:00:00 AM[/TD]
[TD="align: center"]12:00:00 AM[/TD]
[TD="bgcolor: #C0C0C0, align: right"]4[/TD]
[TD="align: right"]23.00[/TD]
[TD="bgcolor: #FFFF99, align: center"]88[/TD]
[TD="align: right"]48[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]1:00:00 AM[/TD]
[TD="align: center"]12:00:00 AM[/TD]
[TD="bgcolor: #C0C0C0, align: right"]5[/TD]
[TD="align: right"]23.00[/TD]
[TD="bgcolor: #FFFF99, align: center"]111[/TD]
[TD="align: right"]71[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]12:00:00 AM[/TD]
[TD="align: center"]12:00:00 AM[/TD]
[TD="bgcolor: #C0C0C0, align: right"]6[/TD]
[TD="align: right"]0.00[/TD]
[TD="bgcolor: #FFFF99, align: center"]111[/TD]
[TD="align: right"]71[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]12:00:00 AM[/TD]
[TD="align: center"]12:00:00 AM[/TD]
[TD="bgcolor: #C0C0C0, align: right"]7[/TD]
[TD="align: right"]0.00[/TD]
[TD="bgcolor: #FFFF99, align: center"]111[/TD]
[TD="align: right"]71[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]10[/TD]
[TD="align: center"]12:00:00 AM[/TD]
[TD="align: center"]12:00:00 AM[/TD]
[TD="bgcolor: #C0C0C0, align: right"]1[/TD]
[TD="align: right"]0.00[/TD]
[TD="bgcolor: #FFFF99, align: center"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]19[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]290[/TD]
[TD="align: right"]0[/TD]

</tbody>

Sheet2​

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

 

[Clarence.Cox]

Anyone?

 

[Clarence.Cox]

I figured it out. I'll post solution when I get home to use the HTML Maker to display the formulas.

 

[Clarence.Cox]

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

	X	Y	Z	AA	AB	AC	AD

<tbody>
[TD="align: center"]3[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]

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

	O	P	Q	R	S	T	U

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFFF99, align: center"]Day Count[/TD]
[TD="bgcolor: #FFFF99, align: center"]Daily Worked[/TD]
[TD="bgcolor: #FFFF99, align: center"]Holiday[/TD]
[TD="bgcolor: #FFFF99, align: center"]Weekly Hours[/TD]
[TD="bgcolor: #FFFF99, align: center"]Weekly Holiday[/TD]
[TD="bgcolor: #FFFF99, align: center"]Regular Hours[/TD]
[TD="bgcolor: #FFFF99, align: center"]OT hours[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]
[TD="bgcolor: #FFFF99, align: center"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #C0C0C0, align: right"]6[/TD]
[TD="align: right"]0.0000[/TD]
[TD="align: right"]0.0000[/TD]
[TD="align: right"]0.0000[/TD]
[TD="align: right"]0.0000[/TD]
[TD="align: right"]0.0000[/TD]
[TD="align: right"]0.0000[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #C0C0C0, align: right"]7[/TD]
[TD="align: right"]9.4406[/TD]
[TD="align: right"]0.0000[/TD]
[TD="align: right"]9.4406[/TD]
[TD="align: right"]0.0000[/TD]
[TD="align: right"]9.4406[/TD]
[TD="align: right"]0.0000[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #C0C0C0, align: right"]1[/TD]
[TD="align: right"]9.0914[/TD]
[TD="align: right"]0.0000[/TD]
[TD="align: right"]0.0000[/TD]
[TD="align: right"]0.0000[/TD]
[TD="align: right"]0.0000[/TD]
[TD="align: right"]0.0000[/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #C0C0C0, align: right"]2[/TD]
[TD="align: right"]8.6131[/TD]
[TD="align: right"]0.0000[/TD]
[TD="align: right"]0.0000[/TD]
[TD="align: right"]0.0000[/TD]
[TD="align: right"]0.0000[/TD]
[TD="align: right"]0.0000[/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #C0C0C0, align: right"]3[/TD]
[TD="align: right"]0.0000[/TD]
[TD="align: right"]0.0000[/TD]
[TD="align: right"]0.0000[/TD]
[TD="align: right"]0.0000[/TD]
[TD="align: right"]0.0000[/TD]
[TD="align: right"]0.0000[/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #C0C0C0, align: right"]4[/TD]
[TD="align: right"]0.0000[/TD]
[TD="align: right"]0.0000[/TD]
[TD="align: right"]0.0000[/TD]
[TD="align: right"]0.0000[/TD]
[TD="align: right"]0.0000[/TD]
[TD="align: right"]0.0000[/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #C0C0C0, align: right"]5[/TD]
[TD="align: right"]0.0000[/TD]
[TD="align: right"]0.0000[/TD]
[TD="align: right"]0.0000[/TD]
[TD="align: right"]0.0000[/TD]
[TD="align: right"]0.0000[/TD]
[TD="align: right"]0.0000[/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: #C0C0C0, align: right"]6[/TD]
[TD="align: right"]0.0000[/TD]
[TD="align: right"]0.0000[/TD]
[TD="align: right"]0.0000[/TD]
[TD="align: right"]0.0000[/TD]
[TD="align: right"]0.0000[/TD]
[TD="align: right"]0.0000[/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: #C0C0C0, align: right"]7[/TD]
[TD="align: right"]0.0000[/TD]
[TD="align: right"]0.0000[/TD]
[TD="align: right"]17.7044[/TD]
[TD="align: right"]0.0000[/TD]
[TD="align: right"]17.7044[/TD]
[TD="align: right"]0.0000[/TD]

[TD="align: center"]12[/TD]
[TD="bgcolor: #C0C0C0, align: right"]1[/TD]
[TD="align: right"]0.0000[/TD]
[TD="align: right"]0.0000[/TD]
[TD="align: right"]0.0000[/TD]
[TD="align: right"]0.0000[/TD]
[TD="align: right"]0.0000[/TD]
[TD="align: right"]0.0000[/TD]

[TD="align: center"]13[/TD]
[TD="bgcolor: #C0C0C0, align: right"]2[/TD]
[TD="align: right"]0.0000[/TD]
[TD="align: right"]0.0000[/TD]
[TD="align: right"]0.0000[/TD]
[TD="align: right"]0.0000[/TD]
[TD="align: right"]0.0000[/TD]
[TD="align: right"]0.0000[/TD]

[TD="align: center"]14[/TD]
[TD="bgcolor: #C0C0C0, align: right"]3[/TD]
[TD="align: right"]0.0000[/TD]
[TD="align: right"]0.0000[/TD]
[TD="align: right"]0.0000[/TD]
[TD="align: right"]0.0000[/TD]
[TD="align: right"]0.0000[/TD]
[TD="align: right"]0.0000[/TD]

[TD="align: center"]15[/TD]
[TD="bgcolor: #C0C0C0, align: right"]4[/TD]
[TD="align: right"]0.0000[/TD]
[TD="align: right"]0.0000[/TD]
[TD="align: right"]0.0000[/TD]
[TD="align: right"]0.0000[/TD]
[TD="align: right"]0.0000[/TD]
[TD="align: right"]0.0000[/TD]

[TD="align: center"]16[/TD]
[TD="bgcolor: #C0C0C0, align: right"]5[/TD]
[TD="align: right"]0.0000[/TD]
[TD="align: right"]0.0000[/TD]
[TD="align: right"]0.0000[/TD]
[TD="align: right"]0.0000[/TD]
[TD="align: right"]0.0000[/TD]
[TD="align: right"]0.0000[/TD]

[TD="align: center"]17[/TD]
[TD="bgcolor: #C0C0C0, align: right"]6[/TD]
[TD="align: right"]0.0000[/TD]
[TD="align: right"]0.0000[/TD]
[TD="align: right"]0.0000[/TD]
[TD="align: right"]0.0000[/TD]
[TD="align: right"]0.0000[/TD]
[TD="align: right"]0.0000[/TD]

[TD="align: center"]18[/TD]
[TD="bgcolor: #C0C0C0, align: right"]####[/TD]
[TD="align: right"]0.0000[/TD]
[TD="align: right"]0.0000[/TD]
[TD="align: right"]0.0000[/TD]
[TD="align: right"]0.0000[/TD]
[TD="align: right"]0.0000[/TD]
[TD="align: right"]0.0000[/TD]

[TD="align: center"]19[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.0000[/TD]
[TD="align: right"]27.1450[/TD]
[TD="align: right"]0.0000[/TD]

</tbody>

Sheet2​

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



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!