Edit Existing VBA to subtract .5 for lunch each if total hours are 6.5 or greater each day

C

cal5859

New Member
Joined
Apr 20, 2018
Messages
1
We purchased a time clock, and the time clock company supplied us with a macro that total the daily hours as well as the total hours for any date range that we choose. Our company policy is if an employee works 6.5 or more hours per day, we subtract .5 hours from that days hours for lunch. I would like to edit the code to perform this action while calculating the hours.
HERE IS A COPY OF THE TIME CLOCK PUNCH REPORT THAT GENERATES FroM OUR TIME CLOCK
[TABLE="width: 709"]
<tbody>[TR]
[TD="colspan: 14"]Time Card Report[/TD]
[/TR]
[TR]
[TD="colspan: 14"]10/1/2015 - 10/30/2015[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]Downloaded:[/TD]
[TD="colspan: 2"]10/27/2015 02:34 PM[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]No: 496386[/TD]
[TD="colspan: 4"]Name: NAME[/TD]
[TD="colspan: 2"]Dept:[/TD]
[TD="colspan: 2"]Shift: NULL[/TD]
[TD="colspan: 4"]Daily totals[/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]Week[/TD]
[TD](IN)[/TD]
[TD](OUT)[/TD]
[TD](IN)[/TD]
[TD](OUT)[/TD]
[TD](IN)[/TD]
[TD](OUT)[/TD]
[TD](IN)[/TD]
[TD](OUT)[/TD]
[TD]Regular Hours[/TD]
[TD]OT Hours[/TD]
[TD]Other Hours[/TD]
[TD]Notes[/TD]
[/TR]
[TR]
[TD]10/01[/TD]
[TD]THU[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/02[/TD]
[TD]FRI[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/03[/TD]
[TD]SAT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/04[/TD]
[TD]SUN[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/05[/TD]
[TD]MON[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/06[/TD]
[TD]TUE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/07[/TD]
[TD]WED[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/08[/TD]
[TD]THU[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/09[/TD]
[TD]FRI[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/10[/TD]
[TD]SAT[/TD]
[TD]5:55[/TD]
[TD]14:58[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/11[/TD]
[TD]SUN[/TD]
[TD]5:58[/TD]
[TD]14:58[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/12[/TD]
[TD]MON[/TD]
[TD]5:57[/TD]
[TD]14:49[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/13[/TD]
[TD]TUE[/TD]
[TD]0:00[/TD]
[TD]0:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/14[/TD]
[TD]WED[/TD]
[TD]14:24[/TD]
[TD]21:01[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/15[/TD]
[TD]THU[/TD]
[TD]14:15[/TD]
[TD]20:15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/16[/TD]
[TD]FRI[/TD]
[TD]0:00[/TD]
[TD]0:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/17[/TD]
[TD]SAT[/TD]
[TD]0:00[/TD]
[TD]0:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/18[/TD]
[TD]SUN[/TD]
[TD]0:00[/TD]
[TD]0:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/19[/TD]
[TD]MON[/TD]
[TD]0:00[/TD]
[TD]0:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/20[/TD]
[TD]TUE[/TD]
[TD]0:00[/TD]
[TD]0:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/21[/TD]
[TD]WED[/TD]
[TD]14:25[/TD]
[TD]21:51[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/22[/TD]
[TD]THU[/TD]
[TD]14:18[/TD]
[TD]21:03[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/23[/TD]
[TD]FRI[/TD]
[TD]0:00[/TD]
[TD]0:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/24[/TD]
[TD]SAT[/TD]
[TD]6:00[/TD]
[TD]15:03[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/25[/TD]
[TD]SUN[/TD]
[TD]6:03[/TD]
[TD]14:59[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/26[/TD]
[TD]MON[/TD]
[TD]0:00[/TD]
[TD]0:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/27[/TD]
[TD]TUE[/TD]
[TD]0:00[/TD]
[TD]0:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/28[/TD]
[TD]WED[/TD]
[TD]0:00[/TD]
[TD]0:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/29[/TD]
[TD]THU[/TD]
[TD]0:00[/TD]
[TD]0:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/30[/TD]
[TD]FRI[/TD]
[TD]0:00[/TD]
[TD]0:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]TOTAL:[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"]Work Total (hrs):[/TD]
[TD][/TD]
[TD="colspan: 2"]Overtime (hrs)[/TD]
[TD][/TD]
[TD="colspan: 2"]Other (hrs):[/TD]
[TD][/TD]
[TD="colspan: 2"]Total (hrs):[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 5"]Employee Signature:[/TD]
[TD="colspan: 5"]Manager Signature:[/TD]
[TD="colspan: 4"]Date:[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]No: 490794[/TD]
[TD="colspan: 4"]Name: NAME[/TD]
[TD="colspan: 2"]Dept:[/TD]
[TD="colspan: 2"]Shift: NULL[/TD]
[TD="colspan: 4"]Daily totals[/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]Week[/TD]
[TD](IN)[/TD]
[TD](OUT)[/TD]
[TD](IN)[/TD]
[TD](OUT)[/TD]
[TD](IN)[/TD]
[TD](OUT)[/TD]
[TD](IN)[/TD]
[TD](OUT)[/TD]
[TD]Regular Hours[/TD]
[TD]OT Hours[/TD]
[TD]Other Hours[/TD]
[TD]Notes[/TD]
[/TR]
[TR]
[TD]10/01[/TD]
[TD]THU[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/02[/TD]
[TD]FRI[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/03[/TD]
[TD]SAT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/04[/TD]
[TD]SUN[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/05[/TD]
[TD]MON[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/06[/TD]
[TD]TUE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/07[/TD]
[TD]WED[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/08[/TD]
[TD]THU[/TD]
[TD]14:18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/09[/TD]
[TD]FRI[/TD]
[TD]6:27[/TD]
[TD]17:14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/10[/TD]
[TD]SAT[/TD]
[TD]0:00[/TD]
[TD]0:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/11[/TD]
[TD]SUN[/TD]
[TD]0:00[/TD]
[TD]0:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/12[/TD]
[TD]MON[/TD]
[TD]6:30[/TD]
[TD]17:34[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/13[/TD]
[TD]TUE[/TD]
[TD]6:19[/TD]
[TD]19:09[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/14[/TD]
[TD]WED[/TD]
[TD]9:55[/TD]
[TD]11:30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/15[/TD]
[TD]THU[/TD]
[TD]0:00[/TD]
[TD]0:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/16[/TD]
[TD]FRI[/TD]
[TD]0:00[/TD]
[TD]0:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/17[/TD]
[TD]SAT[/TD]
[TD]0:00[/TD]
[TD]0:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/18[/TD]
[TD]SUN[/TD]
[TD]0:00[/TD]
[TD]0:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/19[/TD]
[TD]MON[/TD]
[TD]6:26[/TD]
[TD]17:31[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/20[/TD]
[TD]TUE[/TD]
[TD]6:33[/TD]
[TD]17:07[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/21[/TD]
[TD]WED[/TD]
[TD]0:00[/TD]
[TD]0:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/22[/TD]
[TD]THU[/TD]
[TD]6:28[/TD]
[TD]18:33[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/23[/TD]
[TD]FRI[/TD]
[TD]6:25[/TD]
[TD]17:32[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/24[/TD]
[TD]SAT[/TD]
[TD]0:00[/TD]
[TD]0:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/25[/TD]
[TD]SUN[/TD]
[TD]0:00[/TD]
[TD]0:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/26[/TD]
[TD]MON[/TD]
[TD]6:31[/TD]
[TD]18:18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/27[/TD]
[TD]TUE[/TD]
[TD]6:24[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/28[/TD]
[TD]WED[/TD]
[TD]0:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/29[/TD]
[TD]THU[/TD]
[TD]0:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/30[/TD]
[TD]FRI[/TD]
[TD]0:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]TOTAL:[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"]Work Total (hrs):[/TD]
[TD][/TD]
[TD="colspan: 2"]Overtime (hrs)[/TD]
[TD][/TD]
[TD="colspan: 2"]Other (hrs):[/TD]
[TD][/TD]
[TD="colspan: 2"]Total (hrs):[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 5"]Employee Signature:[/TD]
[TD="colspan: 5"]Manager Signature:[/TD]
[TD="colspan: 4"]Date: [/TD]
[/TR]
</tbody>[/TABLE]

HERE IS THE VBA CODE TO CALCULATE THE HOURS


Code: 
' BioTouch Totals


Const FirstPunchColumn = 3
Const cTotalsColumn = 11


Sub CalculateTimeCards()
    formGetFormat.Show
End Sub


Sub CalcTimeCards(bHundredths As Boolean)
    'Application.ScreenUpdating = False
    
    Dim nRow As Integer
    Dim dtIn As Date
    Dim dtOut As Date
    Dim dDayTotal As Double
    Dim dPeriodTotal As Double
    Dim bOut As Boolean


    On Error GoTo ErrorHandler
    
    BlankCount = 0
    nRow = 4
    With Worksheets("Sheet1")
        Do While BlankCount < 10
            If .Cells(nRow, 1) = "" Then
                BlankCount = BlankCount + 1
            Else
                If Left(.Cells(nRow, 1), 3) = "No:" Then
                    BlankCount = 0
                    dPeriodTotal = 0
                    ' Get the punch data
                    nRow = nRow + 2
                    
                    Do While Trim(.Cells(nRow, 1)) <> ""
                        nCol = FirstPunchColumn
                        dDayTotal = 0
                        bOut = False
                        If Trim(.Cells(nRow, nCol)) = "" Then
                            .Cells(nRow, cTotalsColumn) = ""
                        Else
                            ' Iterate the timecard columns
                            Do While Trim(.Cells(nRow, nCol)) <> ""
                                ' Get the punch
                                If Not bOut Then
                                    dtIn = .Cells(nRow, nCol)
                                    bOut = True
                                Else
                                    dtOut = .Cells(nRow, nCol)
                                    dDayTotal = dDayTotal + (dtOut - dtIn)
                                    If dtOut < dtIn Then
                                        ' Crossed midnight. Add a day
                                        dDayTotal = dDayTotal + 1
                                    End If
                                    bOut = False
                                End If
                                nCol = nCol + 1
                            Loop
                            If Not bOut Then
                                If bHundredths Then
                                    .Cells(nRow, cTotalsColumn).NumberFormat = "0.00"
                                    .Cells(nRow, cTotalsColumn) = 24 * dDayTotal
                                Else
                                    .Cells(nRow, cTotalsColumn).NumberFormat = "[h]:mm"
                                    .Cells(nRow, cTotalsColumn) = HoursAndMins(dDayTotal)
                                End If
                                dPeriodTotal = dPeriodTotal + dDayTotal
                            Else
                                .Cells(nRow, cTotalsColumn) = "MP"
                            End If
                        End If
                        nRow = nRow + 1
                    Loop
                    .Cells(nRow, cTotalsColumn).HorizontalAlignment = xlCenter
                    If bHundredths Then
                        .Cells(nRow, cTotalsColumn).NumberFormat = "0.00"
                        .Cells(nRow, cTotalsColumn) = 24 * dPeriodTotal
                    Else
                        .Cells(nRow, cTotalsColumn).NumberFormat = "[h]:mm"
                        .Cells(nRow, cTotalsColumn) = HoursAndMins(dPeriodTotal)
                    End If
                End If
            End If
            nRow = nRow + 1
        Loop
    End With
    Application.ScreenUpdating = True
Exit Sub
ErrorHandler:
    MsgBox "Problem accessing the Time Card Spreadsheet. Make sure that the time cards are opened in the same " & _
        "Excel instance as this macro", vbOKOnly, "Error"
Exit Sub
End Sub




Function HoursAndMins(ByVal d As Double) As String
    Dim Hours As Integer
    Dim Min As Integer
    Dim str1 As String
        
    d = d * 24
    Hours = Int(d)
    Min = Round((d - Hours) * 60, 0)
    If Len(Min) < 2 Then
        HoursAndMins = Hours & ":0" & Min
    Else
        HoursAndMins = Hours & ":" & Min
    End If
End Function
 
Last edited by a moderator:

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
You must log in or register to reply here.

Similar threads

A
  • Question Question
VBA help - Locking & unlocking on certain times of day
2
Replies
17
Views
468
dmt32
D
A
  • Question Question
Add a specified number of working hours to a date & time, given working day start, end and break times and holidays
Replies
2
Views
925
AquaBuoy
A
Sandeep Warrier
  • Question Question
Calculate No. of Working Days based on Shift Pattern
Replies
2
Views
150
Tetra201
Tetra201
O
  • Solved
Travel Duration allowing for more than one day and different time zones
Replies
7
Views
151
myall_blues
myall_blues
N
  • Question Question
Adding to an existing MACRO
Replies
2
Views
125
Northern NY Design
N

Forum statistics

Threads
1,224,820
Messages
6,181,161
Members
453,021
Latest member
Justyna P

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