Hi all,

I'm creating a spreadsheet to work out what hours I've worked and how much I'm earning. At work if I work within 7:30am - 17:30pm I get paid at one rate, and all other times (including all Saturday and Sunday) I get paid a different rate. Also, 30 minutes lunch is taken from whichever period of work is largest (e.g. if I work 9:00 - 18:00 then I'd get 30 minutes taken out of the 7:30-17:30 period).

I'm having problems calculating this without manually separating the hours out and I can't think how it could be done through a formulae, can anyone help?

Many thanks,
Ben

I'm not at my computer now but I would suggest converting the hours into decimals i.e. 17:30 = 17.5

That way you can easily take out the minutes before and after the specified times

Ben

This isn't complete but most of the way there.

Column A is date of start of work
Column B is time of start of work ( 24 hour clock )
Column C is time of end of work ( 24 hour clock )
day_end is the 17:30 time you stated.
day_start is the 07:30 you stated.
rate1 is basic hourly rate cell.
rate2 is out of hours rate cell.

it copes with day of week and going over the normal hours end time along with starting before normal hours



I haven't done the 30 minute deduction . I'm also struggling with anything that isn't a whole hour. So you may need to use numbers as opposed to time.

Hope that helps I'll send you the spread sheet if you like

Thanks Alun,

I'll take a better look at it tomorrow/Monday (my head isn't up to it atm), but it looks good