Rate reduction after 5 hours

[chrisflow]

I have a simple time sheet that has column E for start time, Column F for time finished, coulmn G for time doing more than one job, column H for total time and I for cost.
The rates are £10/hour start to finish + £15/hour for time doing two jobs.

H10 =F10+(E10>F10)-E10 total time
I 10 =H10*240+(G10*120) cost at different rates

This works well for this simple form, my problem is how to drop the both rates to half after 5 hours.

Thanks for your time looking at this

[6StringJazzer]

For the whole 5 hours, or only the time that exceeds 5 hours?

[chrisflow]

Sorry for the time that exceeds 5 hours

[6StringJazzer]

I'm not quite sure how things work with the two different times you have, and what the rule is for what "over 5 hours" means. That is, is it over 5 hours if both figures added together are over 5 hours? Or are they independent?

This formula treats the two figures independently. If total time is over 5 hours, then time over 5 hours is costed at half the rate. If time doing more than one job is over 5 hours, then time over 5 hours is costed at half that rate.

Please Login or Register  to view this content.

This could be streamlined by putting 5/24 (which is 5 hours) into a cell and referring to that cell. You could also put the two rates in two cells, then refer to those rates divided by 2. Those changes would make modifications easier and more reliable if you later have to change the time threshold or the rates.

[chrisflow]

The total time is including travel the second is for the job time, but both rates drop by 50% for any time over 5 hours. If it helps I could use two cells for the rates.
I have tried your code but it instantly shows a value of 75 and I'm confused about the reference to G2 and H2.
Thanks for your help.

[6StringJazzer]

Sorry, my references don't match yours. I set up a sample spreadsheet so I could visualize what you were talking about and my data was in row 2. Further, I had a reference to H5 which was an error and should have been H2.

So my formula for total cost in I10 that would replace yours would be

Please Login or Register  to view this content.

[chrisflow]

Hi StringJazzer thanks again for your help, but now the new code gives me an instant value of 25.
I've attached the sheet to help out.

[daddylonglegs]

Perhaps try this version

=(H10*5+G10*2.5)*24+MIN(H10*24,5)*5+MIN(G10*24,5)*2.5

[6StringJazzer]

My formula did not take into account that there would be blank cells. I have a simple correction for that (add "+0" to force conversion from blank to zero) but daddylonglegs's formula is more elegant so I would go with that.Just for closure:

Please Login or Register  to view this content.

[chrisflow]

Thank you both for your help. Both codes work as needed. Sorry 6StringJazzer for my poor explanation, I should have attached the sheet at the start.