simple formula to calculate overtime pay

[galaxyfan]

Hello all,

I am new to this forum and hope someone can assist with helping me find the correct function to use.

My scenario:
I would like to calculate overtime pay for employees

My problem:
Regular pay applies for 1-40 hours. Time and a half is from 40-51 hours, and over 51 hours is double time.

For double time, I am using the "if" function. =if(a1>51, (a1-51)*a2, 0).

I am struggling with a function for 40-51 hours. I want to multiply anything that lies anything between 40-51 by the time and a half rate.

Hopefully this makes sense. Thanks for your time and assistance, it is appreciated!

[galaxyfan]

P.s.- my apologies in advance if I've posted in the wrong subforum!

[Tank997]

If you put the total hours in a cell and name the cell "Hours" and put the pay rate in a cell and name it "Rate" this formula will give your the total earnings.
=IF(Hours>51,(((Hours-51)*2)+(Hours-(Hours-51)-40)*1.5+40)*Rate,IF(Hours>40,((Hours-40)*1.5+40)*Rate,Hours*Rate))

If you just want total earned hours, this will work.
=IF(Hours>51,(((Hours-51)*2)+(Hours-(Hours-51)-40)*1.5+40),IF(Hours>40,((Hours-40)*1.5+40),Hours))