# TimeSheet-Calculator overtime Excel

#### crilly

##### New Member
[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]

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

#### MARK858

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

#### crilly

##### New Member
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,"")
 Year​ Month​ Date​ Weekend​ 2017 February​ 1 Sat & Sun​ Start Time​ Regular Hours​ Regular Pay (hourly)​ Overtime Pay (hourly)Times1.1/2​ Overtime Pay (hourly) Times 2​ 09:00​ 9 10 15 20 Day​ Date​ In Time​ Out Time​ Break Hrs​ Regular Hrs​ OT Hours​ Total Pay​ Wed​ 1 09:00​ 18:00​ 1.0 8.0 0.0 80.0 Thu​ 2 Fri​ 3 Sat​ 4 09:00​ 18:00​ 1.0 8.0 0.0 120.0 Sun​ 5 09:00​ 18:00​ 1.0 8.0 0.0 160.0

<tbody>
</tbody>

<tbody>
</tbody>

Last edited:

Last edited:

Replies
76
Views
2K
Replies
7
Views
116
Replies
4
Views
56
Replies
3
Views
36
Replies
1
Views
66