Excel Timesheet

March 9, 2009 at 09:46:59
Specs: Windows XP
Does anyone know the formula to calculate double overtime?

See More: Excel Timesheet

Report •

March 9, 2009 at 12:58:36

=2*(Regular Pay Rate)

...but I'm gonna guess that that is not what you are looking for.

Please explain your question in more detail.

Report •

March 9, 2009 at 13:14:28
I appreciate your response. I guess I was a little vague, sorry. Actually, I need the formula for any hours that are over 12 hours worked figured into the double OT column. You know how the timesheet calculates hours after 8 to be OT, well I need it to distinguish between reg OT 8-12 hrs worked and double OT (after 12 hours). I am not worried about the payrate. Is that more clear?Thank you.

Report •

March 9, 2009 at 14:27:55
re You know how the timesheet calculates hours after 8 to be OT...

Actually I don't. Assuming it's a template, if you'll point me to the one you are using, I'll take a look and see how they are calculating OT and see if I can help.

Another option would be to paste the 8-hour OT formula here and I'll see what I can do about making it a 12 hour double OT formula.

Report •

Related Solutions

March 9, 2009 at 15:32:42
Is it "Double Overtime" or Just "Double Time", there is a difference.

Double Overtime is calculated as

((Regular Hourly Rate * 1.5) * 1.5)

While Double Time can be figured as

(Regular Hourly Rate * 2)

Regular Hourly Rate of $10.00

If it is Double Overtime:
Overtime Rate is $15.00
Double Overtime is $22.00

If is is Double Time
Overtime Rate is $15.00
Double Time Rate is $20.00

How it's worded is important.....


Report •

March 10, 2009 at 09:58:32
Can I send you the timesheet? How do I upload my doc on this website?

Report •

March 10, 2009 at 10:08:26
Maybe the better question would be: I need the formula to calulate any hours over 12 to total in a different field. I have a column for total hours worked. The max is that field is 8. The next column is any additional hours over 8. But what I want is for it to only be the hours between 8-12. I want the next column to calculate any after 12. So employee worked 14 hours shift, this is what I want to see:

Column A total hours worked- 14
Column B reg hours- 8
Column C Overtime- 4
Column D Double OT- 2

Really this timesheet is so management knows how to pay for each set of hours. I don't want the formula to calulate any rate of pay, just hours. Does that make more sense? Thank you.

Report •

March 10, 2009 at 10:25:59
Try these:

Assume "Total Hours" in A2

Reg Hours:   =IF(A2<8,A2,8)
Overtime:    =IF(A2>8,(IF(A2>12,4,A2-8)),"")
Double OT:   =IF(A2>12,A2-12,"")

P.S. You can't upload a spreadsheet to this forum. The best you can do is ask someone (nicely) to PM you an email address so you can send the file directly to them.

Report •

March 10, 2009 at 10:57:41
Thank you, thank you, and thank you! It worked. I guess this is why I should have tried harder in math (and they said I wouldn't need it in life, ha)! Thank you for responding despite my inability to articulate what I wanted. I really appreciate it!

Report •

March 10, 2009 at 11:03:43
Actually, I don't know if trying harder in math would have helped.

I'm thinking a course in logic would be more useful for building Nested IF statements. First you need to know how an IF statement works, then you need to be able to logically figure out what to do IF and IF NOT a event happens.

Logic courses were actually my favorite courses in engineering school. Maybe that's why I like to argue so much. ;)

Report •

Ask Question