Need to create a formula that deals with time

[Weasel]

I'm working in an excel sheet that is for payroll. For each person it lists the amount of hours they worked for each day (Saturday - Friday). It looks like this:

Column A    Column B        Column C

Weekday     Time Worked    Overtime
Saturday        4:13 (4 hours, 13 minutes)
Sunday          8:12
Monday          9:20
Total

Anyone who works over 8:00 hours in a day gets overtime. So using my example above, the person should get 0:12 overtime on Sunday and 1:20 overtime on Monday. I need a formula that will automatically put the 0:12 and 1:20 in Column C. I also need a formula that will add up the Time Worked and Overtime columns. Thanks in advance for any help.

 

[Peter_SSs]

Weasel

Something like this?

1. E1 contains 8:00
2. C2 (copied down): =MAX(B2-$E$1,0)
3. B4 (copied across): =SUM(B2:B4)
All formatted as hh:mm

Mr Excel.xls
	A	B	C	D	E	F
1		Time Worked	Overtime		8:00
2		4:12	0:00
3		8:12	0:12
4		9:20	1:20
5		21:44	1:32
6
Overtime

 

[timorrill]

Here's an option that doesn't require the overtime value to be stored in a cell. Assuming that all the time values are formatted as [h]:mm...

Book1
	A	B	C	D
1	Weekday	Time Worked	Overtime
2	Saturday	4:13
3	Sunday	8:12	0:12
4	Monday	9:20	1:20
5	Total	21:45	1:32
Sheet1

 

[Weasel]

I can't get the total formula to work properly since one the total goes over 24 it resets it to 0 since it thinks past midnight. Is there a way to get it to work properly? Thanks

 

[timorrill]

Make sure that all time values are formatted as [h]:mm. Highlight all the time values, including the total values, right click and select "Format Cells...". In the "Number" tab choose "Custom" as the Category and enter "[h]:mm" as the Type, and select okay. This should fix the problem.

Note that h:mm format WILL NOT WORK. You must use [h]:mm.

 

[Weasel]

Thanks, that fix it!