Timesheet - Hour +/-

J

JAGTOO

New Member
Joined
Oct 9, 2017
Messages
2
Hi i have this table for a timesheet with the following formula:


[TABLE="class: cms_table_grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"][/TD]
[TD="align: center"]J[/TD]
[TD="align: center"][/TD]
[TD="align: center"]K[/TD]
[/TR]
[TR]
[TD]Day[/TD]
[TD] In[/TD]
[TD] Out[/TD]
[TD]Formula OT 25%[/TD]
[TD]OT 25%[/TD]
[TD]Formula OT 50%[/TD]
[TD]OT 50%[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]17[/TD]
[TD="align: center"]=SI(I3>=19.1,"3",(I3-H3-9))[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]=(I3-H3-9-J3)[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]19[/TD]
[TD="align: center"]=SI(I3>=19.1,"3",(I3-H3-9))[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]=(I3-H3-9-J3)[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]21[/TD]
[TD="align: center"]=SI(I3>=19.1,"3",(I3-H3-9))[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]=(I3-H3-9-J3)[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]16[/TD]
[TD="align: center"]=SI(I3>=19.1,"3",(I3-H3-9))[/TD]
[TD="align: center"]-1[/TD]
[TD="align: center"]=(I3-H3-9-J3)[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]18.5[/TD]
[TD="align: center"]=SI(I3>=19.1,"3",(I3-H3-9))[/TD]
[TD="align: center"]1.5[/TD]
[TD="align: center"]=(I3-H3-9-J3)[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Total HE 25%[/TD]
[TD="align: center"]5.5[/TD]
[TD="align: center"]Total HE 50%[/TD]
[TD="align: center"]2[/TD]
[/TR]
</tbody>[/TABLE]



It worked fine because it didn´t work with hour format, but know they want something more complex. I don't have much expirience with Excel and I've been trying to change the formula to reflect hour format with no results please help. The table should look something like this.

[TABLE="class: cms_table_grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"][/TD]
[TD="align: center"]J[/TD]
[TD="align: center"][/TD]
[TD="align: center"]K[/TD]
[/TR]
[TR]
[TD]Day[/TD]
[TD]In[/TD]
[TD]Out[/TD]
[TD]Formula OT 25%[/TD]
[TD]OT 25%[/TD]
[TD]Formula OT 50%[/TD]
[TD]OT 50%[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]8:00[/TD]
[TD="align: center"]17:00[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]8:00[/TD]
[TD="align: center"]19:00[/TD]
[TD="align: center"][/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]8:00[/TD]
[TD="align: center"]21:00[/TD]
[TD="align: center"][/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]8:00[/TD]
[TD="align: center"]16:00[/TD]
[TD="align: center"][/TD]
[TD="align: center"]-1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]8:00[/TD]
[TD="align: center"]18:30[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1:30[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]8:15[/TD]
[TD="align: center"]17:00[/TD]
[TD="align: center"][/TD]
[TD="align: center"]-0:15[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]8:40[/TD]
[TD="align: center"]17:00[/TD]
[TD="align: center"][/TD]
[TD="align: center"]-0:40[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]8:00[/TD]
[TD="align: center"]22:20[/TD]
[TD="align: center"][/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"]3:20[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Total OT 25%[/TD]
[TD="align: center"]7:30[/TD]
[TD="align: center"]Total OT 50%[/TD]
[TD="align: center"]5:20[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Total T Deduct[/TD]
[TD="align: center"]1:55[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]


Total OT 25% Should only reflect the positive values in colum J
Total OT 50% Should also refelect the positive values in colum K
Total T Deduct Should only reflect the negative values in colum J

Thanks in advance for your help!!
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Sort by date Sort by votes
You need to explain the rules for overtime.

It looks like (correct me if Im wrong - thats why you need to explain it)

the working day is 0800-1700
The first 2 hours are paid at 25%
Any hours over that time are paid at 50%

However, your formula for Day 3 looks wrong
In time is 8 Out time is 21, that's greater than 19.1 so it should return 3 but your values are 2 and 2 in columns J and K ??????


Is this correct?
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question Question
Creating Formulas for timesheets
Replies
2
Views
257
HighAndWilder
H
T
  • Question Question
What if investment
Replies
2
Views
72
tigerzen
T
C
  • Question Question
TIME LIMITATIONS
Replies
1
Views
89
cooleomter
C
C
  • Question Question
Enter formula on last unique value
Replies
2
Views
220
CaptainGravyBum
C
HappyChappy
  • Solved
tidying up formulas
Replies
4
Views
343
HappyChappy
HappyChappy

Forum statistics

Threads
1,224,823
Messages
6,181,169
Members
453,021
Latest member
Justyna P

Share this page

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