TimeSheet-Calculator overtime Excel

[crilly]

[FONT=&quot]I have a question i get pays[/FONT]
[FONT=&quot]weekdays 8 hrs then overtime[/FONT]
[FONT=&quot]all day Sat Multiple Times @ 1.5[/FONT]
[FONT=&quot]all day Sun Multiple Times @ 2[/FONT]
[FONT=&quot]so weekday Multiple Time 1 then overtime @ 1.5[/FONT]
[FONT=&quot]is there a way to have a TimeSheet-Calculator[/FONT]
[FONT=&quot]for a Month Calendar that will recognise Sat on its own
and sun on its own, so can any one help Please [/FONT]

 

[MARK858]

Post what you have done on creating the Month calendar then we can offer formulas for the overtime.

 

[crilly]

I have a question i get pays
weekdays 8 hrs then overtime
all day Sat Multiple Times @ 1.5
all day Sun Multiple Times @ 2
so weekday Multiple Time 1 then overtime @ 1.5
is there a way to have a TimeSheet-Calculator
for a Month Calendar that will recognise Sat on its own
and sun on its own, so can any one help Please

Wed​

<tbody>
</tbody>

=IFERROR(IF(AND(D16<>””,E16<>""),IF(D16>$C$12+TIME($D$12,($D$12-INT($D$12))*60,0),0,IF(E16>$C$12+TIME($D$12,($D$12-INT($D$12))*60,0),MIN(TIME($D$12,($D$12-INT($D$12))*60,0),($C$12+TIME($D$12,($D$12-INT($D$12))*60,0)-D16)),MIN(IF((E16-$C$12)<0,0,(E16-$C$12)),(E16-D16))))*24,"")-F16,"")
=IF(AND(D16<>””,E16<>""),((IF(D16<$C$12,MIN($C$12-D16,E16-D16),0)+IF(E16>$C$12+TIME($D$12,($D$12-INT($D$12))*60,0),MIN((E16-$C$12-TIME($D$12,($D$12-INT($D$12))*60,0)),(E16-D16)),0))*24),"")
=IFERROR(G16*IF(AND(ISNUMBER(SEARCH(TEXT(B16,”ddd"),$F$9)),Data!$F$2),$F$12,$E$12)+H16*$F$12,"")
Sat
=IFERROR(IF(AND(D19<>””,E19<>”"),IF(D19>$C$12+TIME($D$12,($D$12-INT($D$12))*60,0),0,IF(E19>$C$12+TIME($D$12,($D$12-INT($D$12))*60,0),MIN(TIME($D$12,($D$12-INT($D$12))*60,0),($C$12+TIME($D$12,($D$12-INT($D$12))*60,0)-D19)),MIN(IF((E19-$C$12)<0,0,(E19-$C$12)),(E19-D19))))*24,"")-F19,"")
=IF(AND(D19<>””,E19<>""),((IF(D19<$C$12,MIN($C$12-D19,E19-D19),0)+IF(E19>$C$12+TIME($D$12,($D$12-INT($D$12))*60,0),MIN((E19-$C$12-TIME($D$12,($D$12-INT($D$12))*60,0)),(E19-D19)),0))*24),"")=IFERROR(G19*IF(AND(ISNUMBER(SEARCH(TEXT(B19,"ddd"),$F$9)),Data!$F$2),$F$12,$E$12)+H19*$F$12,"")
Sun
=IFERROR(IF(AND(D22<>”",E22<>""),IF(D22>$C$12+TIME($D$12,($D$12-INT($D$12))*60,0),0,IF(E22>$C$12+TIME($D$12,($D$12-INT($D$12))*60,0),MIN(TIME($D$12,($D$12-INT($D$12))*60,0),($C$12+TIME($D$12,($D$12-INT($D$12))*60,0)-D22)),MIN(IF((E22-$C$12)<0,0,(E22-$C$12)),(E22-D22))))*24,"")-F22,"")
=IF(AND(D22<>”",E22<>""),((IF(D22<$C$12,MIN($C$12-D22,E22-D22),0)+IF(E22>$C$12+TIME($D$12,($D$12-INT($D$12))*60,0),MIN((E22-$C$12-TIME($D$12,($D$12-INT($D$12))*60,0)),(E22-D22)),0))*24),"")
=IFERROR(G22*IF(AND(ISNUMBER(SEARCH(TEXT(B22,"ddd"),$F$9)),Data!$F$2),$F$12,$E$12)+H22*$F$12,"")​

[TABLE="width: 500"]
<tbody>[TR]
[TD]

	09:00​	9	10	15	20
Wed​	1	09:00​	18:00​	1.0	8.0	0.0	80.0
Fri​	3

<tbody>
[TD="bgcolor: #EFEFEF"]
[/TD]
[TD="bgcolor: #EFEFEF"]
[/TD]
[TD="bgcolor: #EFEFEF"]
[/TD]
[TD="bgcolor: #EFEFEF"]
[/TD]
[TD="bgcolor: #EFEFEF"]
[/TD]
[TD="bgcolor: #EFEFEF"]
[/TD]
[TD="bgcolor: #EFEFEF"]
[/TD]
[TD="bgcolor: #EFEFEF"]
[/TD]

[TD="bgcolor: #BDD7EE"]

Year​

[/TD]
[TD="bgcolor: #BDD7EE"]

Month​

[/TD]
[TD="bgcolor: #BDD7EE"]

Date​

[/TD]
[TD="bgcolor: #F7C1C9"]

Weekend​

[/TD]

[TD="bgcolor: #EFEFEF"]
[/TD]
[TD="bgcolor: #EFEFEF"] 2017 [/TD]
[TD="bgcolor: #EFEFEF"]

February​

[/TD]
[TD="bgcolor: #EFEFEF"] 1 [/TD]
[TD="bgcolor: #EFEFEF"]

Sat & Sun​

[/TD]
[TD="bgcolor: #EFEFEF"]
[/TD]
[TD="bgcolor: #EFEFEF"]
[/TD]
[TD="bgcolor: #EFEFEF"]
[/TD]

[TD="bgcolor: #EFEFEF"]
[/TD]
[TD="bgcolor: #FCE4D6"]

Start Time​

[/TD]
[TD="bgcolor: #FCE4D6"]

Regular Hours​

[/TD]
[TD="bgcolor: #FCE4D6"]

Regular Pay (hourly)​

[/TD]
[TD="bgcolor: #FCE4D6"]

Overtime Pay (hourly)Times1.1/2​

[/TD]
[TD="bgcolor: #FCE4D6"]

Overtime Pay (hourly) Times 2​

[/TD]
[TD="bgcolor: #EFEFEF"]
[/TD]
[TD="bgcolor: #EFEFEF"]
[/TD]

[TD="bgcolor: #EFEFEF"]
[/TD]
[TD="bgcolor: #EFEFEF"]
[/TD]
[TD="bgcolor: #EFEFEF"]
[/TD]
[TD="bgcolor: #EFEFEF"]
[/TD]
[TD="bgcolor: #EFEFEF"]
[/TD]
[TD="bgcolor: #EFEFEF"]
[/TD]
[TD="bgcolor: #EFEFEF"]
[/TD]
[TD="bgcolor: #EFEFEF"]
[/TD]

[TD="bgcolor: #EFEFEF"]

Day​

[/TD]
[TD="bgcolor: #EFEFEF"]

Date​

[/TD]
[TD="bgcolor: #EFEFEF"]

In Time​

[/TD]
[TD="bgcolor: #EFEFEF"]

Out Time​

[/TD]
[TD="bgcolor: #EFEFEF"]

Break Hrs​

[/TD]
[TD="bgcolor: #EFEFEF"]

Regular Hrs​

[/TD]
[TD="bgcolor: #EFEFEF"]

OT Hours​

[/TD]
[TD="bgcolor: #EFEFEF"]

Total Pay​

[/TD]

[TD="bgcolor: #EFEFEF"]

Thu​

[/TD]
[TD="bgcolor: #EFEFEF"] 2 [/TD]
[TD="bgcolor: #EFEFEF"]
[/TD]
[TD="bgcolor: #EFEFEF"]
[/TD]
[TD="bgcolor: #EFEFEF"]
[/TD]
[TD="bgcolor: #EFEFEF"]
[/TD]
[TD="bgcolor: #EFEFEF"]
[/TD]
[TD="bgcolor: #EFEFEF"]
[/TD]

[TD="bgcolor: #FFC5C5"]

Sat​

[/TD]
[TD="bgcolor: #FFC5C5"] 4 [/TD]
[TD="bgcolor: #FFC5C5"]

09:00​

[/TD]
[TD="bgcolor: #FFC5C5"]

18:00​

[/TD]
[TD="bgcolor: #FFC5C5"] 1.0 [/TD]
[TD="bgcolor: #FFC5C5"] 8.0 [/TD]
[TD="bgcolor: #FFC5C5"] 0.0 [/TD]
[TD="bgcolor: #FFC5C5"] 120.0 [/TD]

[TD="bgcolor: #FFC5C5"]

Sun​

[/TD]
[TD="bgcolor: #FFC5C5"] 5 [/TD]
[TD="bgcolor: #FFC5C5"]

09:00​

[/TD]
[TD="bgcolor: #FFC5C5"]

18:00​

[/TD]
[TD="bgcolor: #FFC5C5"] 1.0 [/TD]
[TD="bgcolor: #FFC5C5"] 8.0 [/TD]
[TD="bgcolor: #FFC5C5"] 0.0 [/TD]
[TD="bgcolor: #FFC5C5"] 160.0 [/TD]

</tbody>

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Dave Patton]

Excel 2010
	B	C	D	E	F	G	H	I
1							Rate	$10.00
2
3	Date	In Time	Out Time	Break Hrs	Total Hrs	Regular Hrs	OT Hours	Total Pay
4	Wed 07-Mar-18	9:00	18:00	1	8.00	8.00	0.00	$80.00
5	Thu 08-Mar-18	9:00	20:00	1	10.00	8.00	2.00	$110.00
6	Fri 09-Mar-18	9:00	20:00	1	10.00	8.00	2.00	$110.00
7	Sat 10-Mar-18	9:00	18:00	1	8.00	0.00	8.00	$120.00
8	Sun 11-Mar-18	9:00	18:00	1	8.00	0.00	8.00	$160.00
9					44.00	24.00	20.00	$580.00
10
4b

Cell Formulas
Range	Formula
F4	=(D4-C4)*24-E4
G4	=IF(WEEKDAY(B4,2)<6,MIN(F4,8),0)
H4	=F4-G4
I4	=G4*$I$1+H4*$I$1*1.5+(WEEKDAY(B4,2)=7)*H4*$I$1*0.5