timesheet

Trevor3007

Well-known Member
Joined
Jan 26, 2017
Messages
675
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
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]
 
Upvote 0

Excel 2010
BCDEFGH
4DateIn TimeOut TimeBreak HrsTotal HrsRegular HrsOT Hours
57-Mar-187:0016:009.008.001.00
68-Mar-189:0012:003.003.000.00
4bb
Cell Formulas
RangeFormula
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
 
Upvote 0
You did not say what part of the suggestion you had problems with!


Excel 2010
BCDEFGHIJ
1Regular and OT calc
2Rate$10.00
3
4version 1version b
5DateIn TimeOut TimeBreak HrsTotal HrsRegular HrsOT HoursTotal PayTotal Pay
6Wed 07-Mar-187:0016:009.008.001.00$95.00$95.00
7Thu 08-Mar-189:0020:00110.008.002.00$110.00$110.00
8Fri 09-Mar-189:0020:00110.008.002.00$110.00$110.00
9Sat 10-Mar-189:0018:009.000.009.00$135.00$135.00
10Sun 11-Mar-189:0011:002.000.002.00$40.00$40.00
1140.0024.0016.00$490.00$490.00
12
4bbb
Cell Formulas
RangeFormula
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)
 
Upvote 0
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.
 
Upvote 0
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)
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,897
Messages
6,175,271
Members
452,628
Latest member
dd2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top