Needing Excel timecard template help

setishock

Wizard of Wires
Messages
10,726
Location
4321
I need a template to calculate hours and overtime hours based on a 40 hour week. The catch is not to start calculating overtime until 40 hours normal time has been reached.

The routine here where I manage has been when our relief is not in town the 3 office workers have to work 7 days a week for several weeks at a time. Since we went on a check based payroll system, they have been calculating overtime pay and it's out of this world. What I'm wanting to do in a nutshell is show the owner it would be far more cost effective to bring a floater onboard. Someone that would be trained on all 3 shifts and give us some time off. You get real stressed out if you keep that sort of load up for weeks at a time. So it would solve 2 problems with 1 extra person. OK, enough of that.

The end goal is to show, in dollars spent, that it would be smarter to go ahead and hire someone. When you shove a spreadsheet under someone's nose and in hours and minutes along with dollars and cents, that they could save X amount of money in a given time frame. Along with our sanity...

Any ideas?
 
A formula such as
Code:
=IF(SUM(B3:F3)<=40,SUM(B3:F3)*K3,40*K3+(SUM(B3:F3)-40)*1.5)
where the cells B3 thru F3 contain the daily hours and the cell K3 contains the hourly rate should do the trick.

If the total hours worked in the week are 40 or less then multiply total hours worked by the hourly rate. If total hours is greater than 40, then subtract 40 from the total hours worked, multiply that by 1.5 and add to the hourly rate times 40.
 
Last edited:
Back
Top Bottom