Overtime calculations based on different rates.

[mmilbourne]

Good Morning,

I need to create a spreadsheet to track hours worked and overtime, however each job worked could have a different base rate and a different time that overtime starts after. for example

Job 1: Base rate is $306 and overtime starts after 8:00

Job 2: Base rate is $406 and overtime starts after 12:18

I am looking for a way to be able to select what job it is and the sheet will be able to select the correct numbers to do the calculations.

the rate that overtime pays is the same, regardless of the time it starts (.85656 cents a min)  

Thank You,

Matt

[#2]

@mmilbourne 

I used a lookup table. See the attached demo.

[#3]

@HansVogelaar 

Thank You! that worked great, 

I do have one other question; I have attached the sheet I am working with. 

I need to be able to calculate the time spent in a hotel (held away) on a round trip it is the end time of the previous day and the start time of the next day.

Thank You.

Matt M.

[#4]

@HansVogelaar 

[#5]

@mmilbourne 

Can you explain this in more detail, referring to the workbook that you attached?

[#6]

I need to be able to calculate the time between off duty (column n) at any location other than BI 128 (column m), and the on-duty time the next (column l) day if over 16:00 hours. if that makes sense. it's to calculate the time spent in a hotel (held away time). so a round trip is from BI 128 to BI 128 with a stop at any other location.

[#7]

@mmilbourne 

Does this do what you want?

=IF([@[Off Duty Time]]="BI 128",0,MAX(OFFSET([@Date],1,0)+OFFSET([@[On Duty Place]],1,0)-[@Date]-[@[Off Duty Place]]-TIME(16,0,0),0))

[#8]

@HansVogelaar, Yes that works great, Thank You - Matt