Using Excel to Calculate Hours Worked

December 5, 2008 8:10 AM

Excel Filter: Working on a timesheet and trying to calculate regular and overtime pay on a daily basis...kind of

Ok, I am working on a timesheet for my employees. We calculate overtime based on whether they work over 40 hours in a week, not 8 hours in a day. I am trying to figure out an excel formula to watch the total number of hours worked in a week and if it goes over 40 to put the hours up to 40 into the regular hours column and the remainder into the overtime column. And any hours worked in subsequent days put into the overtime column because...well...that is overtime. See an example of my spreadsheet here .

The S column is calculating the hours worked based on the arrive and depart columns. T converts that to a number and U is a running total of hours worked in the week. Column N is hours worked minus Lunch/Break. If you see any easier way to simplify this it would be greatly appreciated.

Thanks!

posted by kasperj74 to computers & internet (12 answers total)

Given that A1 contains the total payable hours worked in a week for an employee, then

if(a1<=40; a1; 40) calculates regular hours, and
if(a1<=40;0;a1-40) calculates overtime hours.

Put each formula into its own column, and then create five more columns:
regular rate for that employee,
overtime rate for that employee, and
regular pay = regular rate times regular hours,
overtime pay = overtime rate times overtime hours, and
total weekly pay = regular pay plus overtime pay.
posted by orthogonality at 8:27 AM on December 5, 2008

orthogonality's solution will work. Alternatively you could use:

regular hours as =min(A1,40)
overtime hours as =max(0,A1-40)
posted by Perplexity at 8:40 AM on December 5, 2008 [1 favorite]

I have it worked out for the week, but I am trying to do it on a daily basis. I should have been more clear...sorry...I want it to populate the reg and OT values in columns J and K, respectively.
posted by kasperj74 at 8:47 AM on December 5, 2008

I would like columns J and K to look like this example , but instead of me manually putting these numbers it for it to automatically calculate it.
posted by kasperj74 at 8:54 AM on December 5, 2008

My "Reg Hours" column would have this formula, starting in row 9:

J9=IF($U9<40, $U9-$U8, IF($U8<40, 40-$U9, 0))

Written out, this should be, "If the hours worked so far are less than forty, enter the hours worked today. If the hours worked so far are greater than forty and the hours worked so far yesterday was less than forty, enter in this column the hours needed to bring this week to 40. Otherwise, record no hours here."

Propogate this down the page.

For the Overtime Hours column, the formula would be this:

K9=$U9-$U8-$J9

In other words, "The overtime hours worked today equal the total hours today minus the total hours yesterday minus the regular time hours today."

I haven't tested these yet, but I'm pretty sure they're close.posted by muddgirl at 10:28 AM on December 5, 2008

OK, in the first formula for regular hours replace

40-$U9

with

$U9-40

and it should work.

For the first day of the week, you can just assume that they won't work any overtime hours, so OT would be "0" and Regular Time would be "$U8"
posted by muddgirl at 10:33 AM on December 5, 2008

Today'sRegularHours=MAX(MIN(RunningTotal,40)-PreviousDay'sRunningTotal,0)

Today'sOT=MAX(0,Today'sRunningTotal-40)-Yesterday'sOT
posted by jon1270 at 10:37 AM on December 5, 2008

Mine's still broken. jon1270's answer works though.
posted by muddgirl at 10:42 AM on December 5, 2008

Whoops, no it doesn't. Or rather, the first one does but the second becomes a problem on the third day with overtime. Gotta refine that...
posted by jon1270 at 10:44 AM on December 5, 2008

Today'sOT=MAX(0,Today'sRunningTotal-40)-MAX(0,Yesterday'sRunningTotal-40)

That's better.
posted by jon1270 at 10:51 AM on December 5, 2008

Thank you all so much!!! It works great!
posted by kasperj74 at 11:14 AM on December 5, 2008

FWIW, I fixed my equation. For regular time it should be

J9=IF(Today<40, Today-Yesterday, IF(Yesterday<40, 40-Yesterday, 0))

The equation for OT is the same.
posted by muddgirl at 12:16 PM on December 5, 2008