Timecard Help

[blinhart]

[FONT=&quot]I am working on a timecard worksheet that calculates the time worked between the start time and end time. I can get it to calculate and round to the nearest quarter hour, but I also want it to max out the regular hours at 12.25 hours than its OT. This is what I have so far that I'm using and just manually changing the regular hours to 12.25:[/FONT]
[FONT=&quot]=ROUND((D45-C45)*24,1) for regular pay[/FONT]
[FONT=&quot]=IFERROR(MROUND((D45-C45+F45-E45+(C45>D45))*24-12.25,0.25),0) for my OT

Any help is appreciated, I've tried several different options and I can't get any formula I've tried to calculate past 12.0 hours in regular hours.[/FONT]

 

[Jadelyn]

For the reg calculation, if all you're trying to do is have it show regular time worked up to 12.25 hrs but no more, you could use MIN to cap it at 12.25:

=MIN(ROUND((D45-C45)*24,1),12.25)

That will calculate your time worked and show the result, unless it exceeds 12.25, then it'll show 12.25.

 

[blinhart]

For the reg calculation, if all you're trying to do is have it show regular time worked up to 12.25 hrs but no more, you could use MIN to cap it at 12.25:

=MIN(ROUND((D45-C45)*24,1),12.25)

That will calculate your time worked and show the result, unless it exceeds 12.25, then it'll show 12.25.

[Thank you, this worked perfectly. Once more question, what it I have 4 punches for the day instead of 2, for instance if someone left in the middle of the shift for a doctor appointment and than clocked back in to finish the remaining hours]

 

[blinhart]

[Thank you, this worked perfectly. Once more question, what it I have 4 punches for the day instead of 2, for instance if someone left in the middle of the shift for a doctor appointment and than clocked back in to finish the remaining hours]

 

[Jadelyn]

Not sure how your timesheet is laid out, but what I did was set up 5 or 6 lines per day (I don't remember exactly, but I wanted to make sure nobody ran out of room), each one of which had its own hours calculation cell, and a separate summary sheet that tallied it up and applied the MIN/MAX stuff for reg/OT per day. If you did something like that, your total hours/OT would be:

=MIN(E45+E46,12.25)
=MAX ((E45+E46)-12.25,0)

Where E45 is the hours worked between the first pair of in/out punches contained in C45 and D45, and E46 is the hours worked between the second pair of in/out punches on the next row in C46 and D46.

The one I did was actually fairly complex because I was accounting for various pay codes like sick and vacation that needed to be excluded from the totals for OT purposes, so for me each line had an in-punch, out-punch, total, and pay code. The in/out cells were editable, the pay code was a dropdown list, and the total was a calculation based on rounding to the quarter-hour. Then the summary tab had a couple of different MIN/MAX based formulas to sort regular worked hours out into reg (up to 8), OT (8-12), and DBL OT (12+). It doesn't sound like yours is quite that much of a headache, but hopefully some of the principles I used can help you.

 

[blinhart]

Not sure how your timesheet is laid out, but what I did was set up 5 or 6 lines per day (I don't remember exactly, but I wanted to make sure nobody ran out of room), each one of which had its own hours calculation cell, and a separate summary sheet that tallied it up and applied the MIN/MAX stuff for reg/OT per day. If you did something like that, your total hours/OT would be:

=MIN(E45+E46,12.25)
=MAX ((E45+E46)-12.25,0)

Where E45 is the hours worked between the first pair of in/out punches contained in C45 and D45, and E46 is the hours worked between the second pair of in/out punches on the next row in C46 and D46.

The one I did was actually fairly complex because I was accounting for various pay codes like sick and vacation that needed to be excluded from the totals for OT purposes, so for me each line had an in-punch, out-punch, total, and pay code. The in/out cells were editable, the pay code was a dropdown list, and the total was a calculation based on rounding to the quarter-hour. Then the summary tab had a couple of different MIN/MAX based formulas to sort regular worked hours out into reg (up to 8), OT (8-12), and DBL OT (12+). It doesn't sound like yours is quite that much of a headache, but hopefully some of the principles I used can help you.

[The first in punch is in column C and Dis the normal out column, unless the person would happen to leave and come back then there are punches in C, D, E, F. I tried this "=MIN(ROUND(((F45-C45)-(E45-D45))*24,0.25),12.25)" and now it's not totally correctly, only giving me 10 hours instead of 10.25 for someone who left for 2 hours of the 12.25 hour shift] - Thanks for helping with this.

 

[Jadelyn]

I think you need to use MROUND instead of ROUND for that calculation method. Sorry I didn't catch that before - I'd used a different method for adding up/rounding for mine when I originally created it, since my company was rounding punches to the nearest quarter hour then calculating time worked on that, where it seems like you're calculating total time then rounding the result to the nearest 0.25. (For example, if an employee clocked in at 8:10, then out at 12:05, they've worked a total of 235 minutes, or 3.92 hrs, which your method rounds up to 4; my method adjusts the punches to 8:15 and 12:00 then calculates based on that, which is 3.75 hrs.)

Try:

=MIN(MROUND(((F45-C45)-(E45-D45))*24,0.25),12.25)

I tested it with punches: 8:30 am, 12:00 pm, 2:00 pm, 8:45 pm and got 10.25.

 

[blinhart]

I think you need to use MROUND instead of ROUND for that calculation method. Sorry I didn't catch that before - I'd used a different method for adding up/rounding for mine when I originally created it, since my company was rounding punches to the nearest quarter hour then calculating time worked on that, where it seems like you're calculating total time then rounding the result to the nearest 0.25. (For example, if an employee clocked in at 8:10, then out at 12:05, they've worked a total of 235 minutes, or 3.92 hrs, which your method rounds up to 4; my method adjusts the punches to 8:15 and 12:00 then calculates based on that, which is 3.75 hrs.)

Try:

=MIN(MROUND(((F45-C45)-(E45-D45))*24,0.25),12.25)

I tested it with punches: 8:30 am, 12:00 pm, 2:00 pm, 8:45 pm and got 10.25.

[=MIN(ROUND(SUM((F45-C45)-(E45-D45))*96,0)/4,12.25) is the formula I got to finally work also. From a different perspective, I like your formula and the way it works. I never thought of using the MROUND funtion, thanks so much. ]

 

[blinhart]

Thanks to everyone that helped out, your time and helpfulness is greatly appreciated.