timesheet

[Trevor3007]

hi & thanks for your help.

Is there a formula so overtime claimable is the extra hours worked outside of 8 hrs?

Example:-


start time end time Total hrs overtime claimable
07:00 16:00 9 1



sorry if it may be obvious , but am not at the sharp end of excel yet!

many thanks again

 

[Zenwood]

Try this

[TABLE="width: 865"]
<colgroup><col span="5"></colgroup><tbody>[TR]
[TD]Start[/TD]
[TD]End[/TD]
[TD]Total[/TD]
[TD]Regular[/TD]
[TD]Overtime[/TD]
[/TR]
[TR]
[TD]7:00[/TD]
[TD]16:00[/TD]
[TD]=(B2-A2)*24[/TD]
[TD]8.0[/TD]
[TD]=C2-D2[/TD]
[/TR]
</tbody>[/TABLE]

 

[Dave Patton]

look at the suggestion in post
https://www.mrexcel.com/forum/excel-questions/1050823-timesheet-calculator-overtime-excel.html

N.B. Regular is min Total and 8 -min(d2,8)

 

[Trevor3007]

thank you for your assistance. Unfortunately the info contained via
https://www.mrexcel.com/forum/excel-...ime-excel.html for me is very confusing and beyond my scope. Thank anyhoo. and maybe some other my pick this up.

have a great day :}

 

[Dave Patton]

Excel 2010
	B	C	D	E	F	G	H
4	Date	In Time	Out Time	Break Hrs	Total Hrs	Regular Hrs	OT Hours
5	7-Mar-18	7:00	16:00		9.00	8.00	1.00
6	8-Mar-18	9:00	12:00		3.00	3.00	0.00
4bb

Cell Formulas
Range	Formula
F5	=(D5-C5)*24-E5
F6	=(D6-C6)*24-E6
G5	=MIN(F5,8)
G6	=IF(WEEKDAY(B6,2)<6,MIN(F6,8),0)
H5	=F5-G5
H6	=F6-G6

 

[Dave Patton]

You did not say what part of the suggestion you had problems with!

Excel 2010
	B	C	D	E	F	G	H	I	J
1							Regular and OT calc
2							Rate	$10.00
3
4								version 1	version b
5	Date	In Time	Out Time	Break Hrs	Total Hrs	Regular Hrs	OT Hours	Total Pay	Total Pay
6	Wed 07-Mar-18	7:00	16:00		9.00	8.00	1.00	$95.00	$95.00
7	Thu 08-Mar-18	9:00	20:00	1	10.00	8.00	2.00	$110.00	$110.00
8	Fri 09-Mar-18	9:00	20:00	1	10.00	8.00	2.00	$110.00	$110.00
9	Sat 10-Mar-18	9:00	18:00		9.00	0.00	9.00	$135.00	$135.00
10	Sun 11-Mar-18	9:00	11:00		2.00	0.00	2.00	$40.00	$40.00
11					40.00	24.00	16.00	$490.00	$490.00
12
4bbb

Cell Formulas
Range	Formula
F6	=(D6-C6)*24-E6
F7	=(D7-C7)*24-E7
F8	=(D8-C8)*24-E8
F9	=(D9-C9)*24-E9
F10	=(D10-C10)*24-E10
F11	=SUM(F6:F10)
G6	=IF(WEEKDAY(B6,2)<6,MIN(F6,8),0)
G7	=IF(WEEKDAY(B7,2)<6,MIN(F7,8),0)
G8	=IF(WEEKDAY(B8,2)<6,MIN(F8,8),0)
G9	=IF(WEEKDAY(B9,2)<6,MIN(F9,8),0)
G10	=IF(WEEKDAY(B10,2)<6,MIN(F10,8),0)
G11	=SUM(G6:G10)
H6	=F6-G6
H7	=F7-G7
H8	=F8-G8
H9	=F9-G9
H10	=F10-G10
H11	=SUM(H6:H10)
I6	=G6*$I$2+H6*$I$2*1.5+(WEEKDAY(B6,2)=7)*H6*$I$2*0.5
I7	=G7*$I$2+H7*$I$2*1.5+(WEEKDAY(B7,2)=7)*H7*$I$2*0.5
I8	=G8*$I$2+H8*$I$2*1.5+(WEEKDAY(B8,2)=7)*H8*$I$2*0.5
I9	=G9*$I$2+H9*$I$2*1.5+(WEEKDAY(B9,2)=7)*H9*$I$2*0.5
I10	=G10*$I$2+H10*$I$2*1.5+(WEEKDAY(B10,2)=7)*H10*$I$2*0.5
I11	=SUM(I6:I10)
J6	=IF(WEEKDAY(B6,2)<7,G6*$I$2+H6*$I$2*1.5,G6*$I$2+H6*$I$2*2)
J7	=IF(WEEKDAY(B7,2)<7,G7*$I$2+H7*$I$2*1.5,G7*$I$2+H7*$I$2*2)
J8	=IF(WEEKDAY(B8,2)<7,G8*$I$2+H8*$I$2*1.5,G8*$I$2+H8*$I$2*2)
J9	=IF(WEEKDAY(B9,2)<7,G9*$I$2+H9*$I$2*1.5,G9*$I$2+H9*$I$2*2)
J10	=IF(WEEKDAY(B10,2)<7,G10*$I$2+H10*$I$2*1.5,G10*$I$2+H10*$I$2*2)
J11	=SUM(J6:J10)

 

[Trevor3007]

Hi Dave,
Thanks for getting back to me. Thanks to you i have managed to sort. The reason was i had the incorrect format and this was effecting my formulas. Many thanks again.

 

[Dave Patton]

Thanks for the feedback.
If you are using the version b of the formula,
you can delete an unnecessary part of the formula.

=IF(WEEKDAY(B6,2)<7,G6*$I$2+H6*$I$2*1.5,H6*$I$2*2)