x404.co.uk
http://www.x404.co.uk/forum/

Excel/Spreadsheet calculating hours
http://www.x404.co.uk/forum/viewtopic.php?f=3&t=11689		 Page 1 of 1
Author:  forquare1 [ Fri Dec 03, 2010 4:54 pm ]
Post subject:  Excel/Spreadsheet calculating hours
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
Author:  Fogmeister [ Fri Dec 03, 2010 6:41 pm ]
Post subject:  Excel/Spreadsheet calculating hours
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
Author:  AlunD [ Sat Dec 04, 2010 4:25 pm ]
Post subject:  Re: Excel/Spreadsheet calculating hours
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

Code:
=IF(WEEKDAY(A15)=1,rate2*HOUR(C15-B15),IF(WEEKDAY(A15)=7,rate2*HOUR(C15-B15),IF(C15>day_end,SUM((HOUR(day_end-B15)*rate1)+(HOUR(C15-day_end)*rate2)),IF(B15>day_end,HOUR(C15-B15)*rate2,IF(B15<day_start,SUM((HOUR(day_start-B15)*rate2)+(HOUR(C15-day_start)*rate1)),HOUR(C15-B15)*rate1)))))


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

Hope that helps I'll send you the spread sheet if you like
Author:  forquare1 [ Sat Dec 04, 2010 6:05 pm ]
Post subject:  Re: Excel/Spreadsheet calculating hours
Thanks Alun,

I'll take a better look at it tomorrow/Monday (my head isn't up to it atm), but it looks good :)
Page 1 of 1 All times are UTC
Powered by phpBB® Forum Software © phpBB Group
https://www.phpbb.com/