Overtime formula once 40 hours have been reached.

[tasha2515]

hello,
I am trying to calculate the Ot hours once the Regular Hours have reached 40. If you could help with this that would be amazing!! we work shift work so our hours are from 7-3 3-11 11-7 so the time must be able to calculate into another day. we have figured out a few of the formulas, we just cant get the OT to start calculating only after 40 hours have been reached not after 8 hours per day have been reached. Thank you in advanced!! here is what our time sheet looks like
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]date[/TD]
[TD]time in[/TD]
[TD]lunch out[/TD]
[TD]lunch in [/TD]
[TD]time out[/TD]
[TD]total hours[/TD]
[TD]regular hours[/TD]
[TD]OT hours[/TD]
[/TR]
[TR]
[TD]mon[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]tue[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]wed[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]thu[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]fri[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]sat[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]sun[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]totals[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[mrhstn]

Perhaps calculate the OT first then subtract it from regular hours. Like so...

Book1
	A	B	C	D	E	F	G	H
1	Date	Time In	Lunch Out	Lunch In	Time Out	Total Hours	Regular	OT
2	Monday	07:00	11:00	11:30	15:30	8	8	0
3	Tuesday	07:00	11:00	11:30	15:30	8	8	0
4	Wednesday	07:00	11:00	11:30	17:30	10	10	0
5	Thursday	07:00	11:00	11:30	15:30	8	8	0
6	Friday	07:00	11:00	11:30	15:30	8	6	2
7	Saturday	07:00	11:00	11:30	15:30	8	0	10
8	Sunday	07:00	11:00	11:30	15:30	8	0	18
Sheet1

Cell Formulas
Range	Formula
F2	=(MOD(E2-B2, 1)-MOD(D2-C2, 1))*24
G2	=MAX(F2-H2, 0)
H2	=IF(SUM($F$2:F2)>40, SUM($F$2:F2)-40, 0)

 

[tasha2515]

i added these into my spread sheet and all it gave me was 0.00 and #VALUE ! it would not calculate any hours at all aside form fri, sat and sun that have no times in them. for some odd reason it calculated for those days but not for ones with times, also you do not have to include lunch as we get paid for it, its just on the time sheet as a requirement from our staffing company.

 

[mrhstn]

I have my data starting in row 2, some adjustment would be required to suit your exact need. I also use actual times, some companies just enter the hour for start time, again some adjustment might be needed.

In the above the formulas expand as you copy down. Here is the full formula list with some days deleted to demonstrate integrity of the formulas.

Book1
	A	B	C	D	E	F	G	H
1	Date	Time In	Lunch Out	Lunch In	Time Out	Total Hours	Regular	OT
2	Monday					0	0	0
3	Tuesday					0	0	0
4	Wednesday					0	0	0
5	Thursday	07:00	11:00	11:30	15:30	8.5	8.5	0
6	Friday	07:00	11:00	11:30	15:30	8.5	8.5	0
7	Saturday					0	0	0
8	Sunday					0	0	0
Sheet1

Cell Formulas
Range	Formula
F2	=MOD(E2-B2, 1)*24
F3	=MOD(E3-B3, 1)*24
F4	=MOD(E4-B4, 1)*24
F5	=MOD(E5-B5, 1)*24
F6	=MOD(E6-B6, 1)*24
F7	=MOD(E7-B7, 1)*24
F8	=MOD(E8-B8, 1)*24
G2	=MAX(F2-H2, 0)
G3	=MAX(F3-H3, 0)
G4	=MAX(F4-H4, 0)
G5	=MAX(F5-H5, 0)
G6	=MAX(F6-H6, 0)
G7	=MAX(F7-H7, 0)
G8	=MAX(F8-H8, 0)
H2	=IF(SUM($F$2:F2)>40, SUM($F$2:F2)-40, 0)
H3	=IF(SUM($F$2:F3)>40, SUM($F$2:F3)-40, 0)
H4	=IF(SUM($F$2:F4)>40, SUM($F$2:F4)-40, 0)
H5	=IF(SUM($F$2:F5)>40, SUM($F$2:F5)-40, 0)
H6	=IF(SUM($F$2:F6)>40, SUM($F$2:F6)-40, 0)
H7	=IF(SUM($F$2:F7)>40, SUM($F$2:F7)-40, 0)
H8	=IF(SUM($F$2:F8)>40, SUM($F$2:F8)-40, 0)

 

[tasha2515]

i dont understand how this isnt working.. i put every formula in the way you have it yet its still not calculating correctly i need to find a way to show you what our exact time sheet looks like.

---

---

 

[Peter_SSs]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules. Be sure to follow & read the link at the end of the rule too!

Please post back here with links to all your posts that asked this same question in other forums.

 

[codeliftsleep]

I'd just handle the changed event of the cell in VBA personally, but that's coming from a programmer.

 

[James006]

Hello,

Just noticed your problem was solved at Ozgrid ...

Time sheet in Excel - Page 2

Is this right ?