Pay per minute time sheet with two rates

[kalmar27615]

Hi all! I am new to this forum, although I have used it to solve a lot of my problems previously. Thank you all very much for your help in posting different solutions! I am not sure how to approach a solution for this: I am trying to create a time sheet that will calculate rate per minute. sounds easy enough, but here is the trick: there are two rates within one hour. First rate (0.12 cents/min) will be applicable for 25 "clocked in" minutes within that hour. Once the 25 "clocked in" minutes elapse, there is a different rate (0.30 c/min). The clock-in/out will be happening numerous times within each hour. Another issue I have is if I clock in at 8:55 and clock out at 9:10, I need the spreadsheet to know that the new hour started and there are two rates within that one punch: so it would be 5 minutes @ 0.30 c/m and 10 minutes @ 0.12 c/m. the clock in can happen any time within the hour for several times. for example: I can clock in at 8:15 and clock out at 8:20 - this is a total of 5 "paid" minutes. then i can clock in at 8:30 and clock out at 8:50 - this is a total of 25 "paid" minutes at a rate of 0.12c/m. after this 25 "paid" minutes have passed, the rate changes to 0.30c/m. so when i clock in at 8:52 and clock out at 8:58 the "paid" minutes (6 in this case) will be at a rate of 0.30c/min. once the new hour starts, the first 25 "paid" minutes have to pass again before the 0.30 rate will take place, unless i'm already clocked in from the previous hour: if i clock in at 8:58 and already at 0.30 rate and clock out at 9:09 then i will have 2 minutes at 0.30 and 9 minutes at 0.12. I am not sure which way to turn here, so any advice would be greatly appreciated.



[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TH="align: center"]Time In[/TH]
[TH="align: center"]Time Out[/TH]
[TH="align: center"]Rate @ 0.12c/min[/TH]
[TH="align: center"]Rate @ 0.30c/min[/TH]
[TH="align: center"]Pay[/TH]
[/TR]
[TR]
[TD]8:01[/TD]
[TD]8:08[/TD]
[TD]7[/TD]
[TD]0[/TD]
[TD]$0.84[/TD]
[/TR]
[TR]
[TD]8:10[/TD]
[TD]8:23[/TD]
[TD]13[/TD]
[TD]0[/TD]
[TD]$1.56[/TD]
[/TR]
[TR]
[TD]8:24[/TD]
[TD]8:34[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]$2.10[/TD]
[/TR]
[TR]
[TD]8:36[/TD]
[TD]8:45[/TD]
[TD]0[/TD]
[TD]9[/TD]
[TD]$2.70[/TD]
[/TR]
[TR]
[TD]8:49[/TD]
[TD]8:58[/TD]
[TD]0[/TD]
[TD]9[/TD]
[TD]$2.70[/TD]
[/TR]
[TR]
[TD]8:56[/TD]
[TD]9:10[/TD]
[TD]10[/TD]
[TD]4[/TD]
[TD]$2.40[/TD]
[/TR]
[TR]
[TD]9:11[/TD]
[TD]9:20[/TD]
[TD]9[/TD]
[TD]0[/TD]
[TD]$1.08[/TD]
[/TR]
[TR]
[TD]9:21[/TD]
[TD]9:40[/TD]
[TD]6[/TD]
[TD]13[/TD]
[TD]$4.62[/TD]
[/TR]
[TR]
[TD]9:45[/TD]
[TD]10:15[/TD]
[TD]15[/TD]
[TD]15[/TD]
[TD]$6.30[/TD]
[/TR]
</tbody>[/TABLE]