Excel timesheet's - working out overtime.

[nome_]

I don't know if this is possible, but I do only have very basic Excel knowledge (using Excel Mac 2011 - learnt how to use it when I was 11 - 12 years ago - on a PC!).

We have just changed the pay system at work and over-time will now be paid on any hours worked outside of 9am-5pm (instead of anything over 8hrs) is there a way that i can use excel to work this out?
Ideally by only have the employees enter the hours they worked - in two columns (start time and finish time) then have this broken down in a table underneath in totals for the week.

Please let me know if this is possible, any help would be appreciated.

Thanks.

 

[ChrisOswald]

Here's something to start you out with: The 'trick' to working with time in excel is to remember that everything is stored as the numbers of days, so 12 hours = .5 That's why you see the 24 throughout the formulas.

The summarys would just be done with sum's, vlookup's and/or sumif's: it all depends on how you structure your workbook and whatever rules didn't get into your post (for example, someone taking 3 hours off in the middle of the day).

Excel Workbook
	A	B	C	D	E
1	start	finish	Total Hours	Hours Overtime	hours Regular time
2	8:00 AM	5:00 PM	9.00	1.00	8.00
3	7:00 AM	3:00 PM	8.00	2.00	6.00
4	10:00 AM	3:00 PM	5.00	0.00	5.00
5	10:00 AM	7:00 PM	9.00	2.00	7.00
6	6:00 AM	7:00 PM	13.00	5.00	8.00
Sheet4

Excel 2007

Cell Formulas
Range	Formula
C2	=24*(B2-A2)
D2	=24*(MAX((9/24)-A2,0)+MAX(B2-(17/24),0))
E2	=C2-D2