I am trying to create a spreadsheet which calculates overtime and overtime payment from total hours worked. I have my total hours worked formula in cell L5 for instance which is in hh:mm format.
A standard day is 9 hours and overtime is given as a payment, say £5.00, for every half hour increment, beginning at 1 min past the half hour. For the first half hour it is a lower rate of payment, say £2.50.
ie..
Hours worked: 08:00 - 17:00 = £0 overtime.
Hours worked: 0800 - 17:01 (up to 17:30) = £2.50 overtime.
Hours worked: 0800 - 17:31 = £7.50 overtime.
Hours worked: 0800 - 18:01 = £12.50 overtime.
So with this I then want: Overtime in cell M5 (which will need to be rounded to each higher half hour if a minute into that half hour) and Overtime payment in N5 in currency.
I've been trying to do this for myself for a while and I just can't get it right so would really appreciate the help!
Bookmarks