TimeSheet-Calculator overtime Excel

crilly

New Member
Joined
Nov 26, 2009
Messages
21
[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 :cool:[/FONT]
 

Some videos you may like

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
Joined
Nov 12, 2010
Messages
13,324
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Post what you have done on creating the Month calendar then we can offer formulas for the overtime.
 

crilly

New Member
Joined
Nov 26, 2009
Messages
21
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 :cool:
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
9101520

















Day
Date
In Time
Out Time
Break Hrs
Regular Hrs
OT Hours
Total Pay
Wed
1
09:00
18:00
1.08.00.080.0
Thu
2





Fri
3





Sat
4
09:00
18:00
1.08.00.0120.0
Sun
5
09:00
18:00
1.08.00.0160.0

<tbody>
</tbody>

<tbody>
</tbody>
 
Last edited:

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,239
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: center;color: #305496;background-color: #EFEFEF;;">Rate</td><td style="text-align: right;;">$10.00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: center;color: #305496;background-color: #EFEFEF;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="font-weight: bold;text-align: center;color: #305496;background-color: #EFEFEF;;">Date</td><td style="font-weight: bold;text-align: center;color: #305496;background-color: #EFEFEF;;">In Time</td><td style="font-weight: bold;text-align: center;color: #305496;background-color: #EFEFEF;;">Out Time</td><td style="font-weight: bold;text-align: center;color: #305496;background-color: #EFEFEF;;">Break Hrs</td><td style="font-weight: bold;text-align: center;color: #305496;background-color: #EFEFEF;;">Total Hrs</td><td style="font-weight: bold;text-align: center;color: #305496;background-color: #EFEFEF;;">Regular Hrs</td><td style="font-weight: bold;text-align: center;color: #305496;background-color: #EFEFEF;;">OT Hours</td><td style="font-weight: bold;text-align: center;color: #305496;background-color: #EFEFEF;;">Total Pay</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;background-color: #FAFAFA;;">Wed 07-Mar-18</td><td style="text-align: center;background-color: #FAFAFA;;">9:00</td><td style="text-align: center;background-color: #FAFAFA;;">18:00</td><td style="text-align: right;background-color: #FAFAFA;;">1</td><td style="text-align: right;background-color: #FAFAFA;;">8.00</td><td style="text-align: right;background-color: #FAFAFA;;">8.00</td><td style="text-align: right;background-color: #FAFAFA;;">0.00</td><td style="text-align: right;background-color: #FAFAFA;;">$80.00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;background-color: #FAFAFA;;">Thu 08-Mar-18</td><td style="text-align: center;background-color: #FAFAFA;;">9:00</td><td style="text-align: center;background-color: #FAFAFA;;">20:00</td><td style="text-align: right;background-color: #FAFAFA;;">1</td><td style="text-align: right;background-color: #FAFAFA;;">10.00</td><td style="text-align: right;background-color: #FAFAFA;;">8.00</td><td style="text-align: right;background-color: #FAFAFA;;">2.00</td><td style="text-align: right;background-color: #FAFAFA;;">$110.00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;background-color: #FAFAFA;;">Fri 09-Mar-18</td><td style="text-align: center;background-color: #FAFAFA;;">9:00</td><td style="text-align: center;background-color: #FAFAFA;;">20:00</td><td style="text-align: right;background-color: #FAFAFA;;">1</td><td style="text-align: right;background-color: #FAFAFA;;">10.00</td><td style="text-align: right;background-color: #FAFAFA;;">8.00</td><td style="text-align: right;background-color: #FAFAFA;;">2.00</td><td style="text-align: right;background-color: #FAFAFA;;">$110.00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;background-color: #FAFAFA;;">Sat 10-Mar-18</td><td style="text-align: center;background-color: #FAFAFA;;">9:00</td><td style="text-align: center;background-color: #FAFAFA;;">18:00</td><td style="text-align: right;background-color: #FAFAFA;;">1</td><td style="text-align: right;background-color: #FAFAFA;;">8.00</td><td style="text-align: right;background-color: #FAFAFA;;">0.00</td><td style="text-align: right;background-color: #FAFAFA;;">8.00</td><td style="text-align: right;background-color: #FAFAFA;;">$120.00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;background-color: #FAFAFA;;">Sun 11-Mar-18</td><td style="text-align: center;background-color: #FAFAFA;;">9:00</td><td style="text-align: center;background-color: #FAFAFA;;">18:00</td><td style="text-align: right;background-color: #FAFAFA;;">1</td><td style="text-align: right;background-color: #FAFAFA;;">8.00</td><td style="text-align: right;background-color: #FAFAFA;;">0.00</td><td style="text-align: right;background-color: #FAFAFA;;">8.00</td><td style="text-align: right;background-color: #FAFAFA;;">$160.00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">44.00</td><td style="text-align: right;;">24.00</td><td style="text-align: right;;">20.00</td><td style="text-align: right;;">$580.00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:1.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">4b</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">F4</th><td style="text-align:left">=(<font color="Blue">D4-C4</font>)*24-E4</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">G4</th><td style="text-align:left">=IF(<font color="Blue">WEEKDAY(<font color="Red">B4,2</font>)<6,MIN(<font color="Red">F4,8</font>),0</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">H4</th><td style="text-align:left">=F4-G4</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">I4</th><td style="text-align:left">=G4*$I$1+H4*$I$1*1.5+(<font color="Blue">WEEKDAY(<font color="Red">B4,2</font>)=7</font>)*H4*$I$1*0.5</td></tr></tbody></table></td></tr></table><br />
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,108,974
Messages
5,525,990
Members
409,673
Latest member
Riseee

This Week's Hot Topics

Top