Calculate overtime rates with different hourly rate (2am to 5am)

New Contributor

I need to calculate overtime with different hourly rate from 2am to 5am. I have been breaking my head on this. Can you help?


20% additional hourly rate for overtime hoursUpto 50 hours a week
15% additional hourly rate for overtime hoursAbove 50 hours a week
25% additional hourly rate for overtime hoursBetween 2am to 5am


@Hans Vogelaar 

2 Replies
best response confirmed by sid_asks (New Contributor)


The file that you sent me looks like homework, so I will just give you the formula for the overtime hours between 2:00 AM and 5:00 AM.

You should try to calculate the totals yourself - you'll learn more that way.


I corrected some values in the file:

I entered 4:00 AM in C9, 5:00 AM in L3 and 2:00 AM in L4. The original value in each of these cells contained a date as well as a time. I also removed the date component from D9:D22.


See the attached version.

@Hans Vogelaar 


Thank you so much. It works like a charm.