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