Timesheet GURU needed

HappyChappy

HappyChappy

Active Member
Joined
Jan 26, 2013
Messages
383
Office Version
  1. 2019
  2. 2010
  3. 2007
Platform
  1. Windows
I have a weekly timesheet running on excel 2010 windows 7 for the most part it works great but it does need to be polished a little can anyone help.
the main issue i have is that a break of 45 min must be taken off and the cash deducted from the total. This is fine generally except the day is paid as a min 8 hour shift therefore if i work 7 hours I would get paid for the 8 hours with no 45min deduction.
this is the bit i have trouble with. I have the spreadsheet and can email it to you as i think it would make more sence to see it up close to see where it falls down.
If anyone would care to take a look at it for me that would be great as its driving me mad.

[TABLE="width: 1471"]
<tbody>[TR]
[TD="class: xl63, width: 592, bgcolor: transparent, colspan: 6"]WEEKLY ACCOUNTABILITY/DISCLAIMER FOR HOURS/SHIFTS WORKED
[/TD]
[TD="width: 104, bgcolor: transparent"][/TD]
[TD="width: 77, bgcolor: transparent"][/TD]
[TD="width: 77, bgcolor: transparent"][/TD]
[TD="width: 77, bgcolor: transparent"][/TD]
[TD="width: 77, bgcolor: transparent"][/TD]
[TD="width: 77, bgcolor: transparent"][/TD]
[TD="width: 77, bgcolor: transparent"][/TD]
[TD="width: 77, bgcolor: transparent"][/TD]
[TD="width: 77, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 85, bgcolor: transparent"][/TD]
[TD="width: 107, bgcolor: transparent"][/TD]
[TD="width: 69, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, colspan: 2"]DRIVERS NAME
[/TD]
[TD="class: xl92, bgcolor: transparent, colspan: 2"]Mark Thomas
[/TD]
[TD="class: xl63, bgcolor: transparent"]W/C
[/TD]
[TD="class: xl64, bgcolor: transparent"]27/01/2013
[/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 320, bgcolor: transparent, colspan: 4"]WTD YOUR DUTY TIME MINUS POA BREAKS PAID TIME YOUR DUTY TIME MINUS BREAKS POA MUST BE DECLARED OVER 15 MINUTE BLOCKS
[/TD]
[TD="class: xl65, width: 69, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, width: 88, bgcolor: transparent"]Working Day
[/TD]
[TD="class: xl67, width: 89, bgcolor: transparent"]Date
[/TD]
[TD="class: xl67, width: 87, bgcolor: transparent"]TACHO Type
[/TD]
[TD="class: xl67, width: 173, bgcolor: transparent"]Client Site
[/TD]
[TD="class: xl67, width: 78, bgcolor: transparent"]Start
[/TD]
[TD="class: xl67, width: 77, bgcolor: transparent"]Finish
[/TD]
[TD="class: xl68, width: 104, bgcolor: yellow"]Std Rate Hours Before Break
[/TD]
[TD="class: xl68, width: 77, bgcolor: yellow"]Std Rate Hours
[/TD]
[TD="class: xl68, width: 77, bgcolor: yellow"]Std Total
[/TD]
[TD="class: xl68, width: 77, bgcolor: yellow"]O/T Rate Hours
[/TD]
[TD="class: xl68, width: 77, bgcolor: yellow"]Round to 15 Min
[/TD]
[TD="class: xl68, width: 77, bgcolor: yellow"]Round to 15 Min
[/TD]
[TD="class: xl68, width: 77, bgcolor: yellow"]O/T Total
[/TD]
[TD="class: xl68, width: 77, bgcolor: yellow"]POA don't print
[/TD]
[TD="class: xl68, width: 77, bgcolor: yellow"]Break don't print
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]POA
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]Break
[/TD]
[TD="class: xl67, width: 85, bgcolor: transparent"]Total WTD Time
[/TD]
[TD="class: xl69, width: 107, bgcolor: transparent"]Paid Duty Time
[/TD]
[TD="class: xl65, width: 69, bgcolor: transparent"]Clocked Hours
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]Daily Total
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl70, width: 88, bgcolor: transparent"]SUNDAY
[/TD]
[TD="class: xl71, width: 89, bgcolor: transparent"]27/01/2013
[/TD]
[TD="class: xl72, width: 87, bgcolor: transparent"][/TD]
[TD="class: xl72, width: 173, bgcolor: transparent"][/TD]
[TD="class: xl73, width: 78, bgcolor: transparent"][/TD]
[TD="class: xl73, width: 77, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: yellow"]0:00
[/TD]
[TD="class: xl74, bgcolor: yellow"]0
[/TD]
[TD="class: xl75, bgcolor: yellow"]0.00
[/TD]
[TD="class: xl74, bgcolor: yellow"]0:00
[/TD]
[TD="class: xl74, bgcolor: yellow"]0:00
[/TD]
[TD="class: xl74, bgcolor: yellow"]0:00
[/TD]
[TD="class: xl75, bgcolor: yellow"]0.00
[/TD]
[TD="class: xl75, bgcolor: yellow"][/TD]
[TD="class: xl75, bgcolor: yellow"][/TD]
[TD="class: xl76, bgcolor: white"]0:00
[/TD]
[TD="class: xl77, bgcolor: white"]0:00
[/TD]
[TD="class: xl77, bgcolor: white"]0
[/TD]
[TD="class: xl78, bgcolor: white"]0
[/TD]
[TD="class: xl79, width: 69, bgcolor: transparent"]0:00
[/TD]
[TD="class: xl80, width: 64, bgcolor: transparent"]0
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl81, width: 88, bgcolor: transparent"]MONDAY
[/TD]
[TD="class: xl82, width: 89, bgcolor: transparent"]28/01/2013
[/TD]
[TD="class: xl83, width: 87, bgcolor: transparent"]DIGI
[/TD]
[TD="class: xl83, width: 173, bgcolor: transparent"][/TD]
[TD="class: xl84, width: 78, bgcolor: transparent"][/TD]
[TD="class: xl84, width: 77, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: yellow"]0:00
[/TD]
[TD="class: xl74, bgcolor: yellow"]0
[/TD]
[TD="class: xl75, bgcolor: yellow"]0.00
[/TD]
[TD="class: xl74, bgcolor: yellow"]0:00
[/TD]
[TD="class: xl74, bgcolor: yellow"]0:00
[/TD]
[TD="class: xl74, bgcolor: yellow"]0:00
[/TD]
[TD="class: xl75, bgcolor: yellow"]0.00
[/TD]
[TD="class: xl75, bgcolor: yellow"][/TD]
[TD="class: xl75, bgcolor: yellow"]45.00
[/TD]
[TD="class: xl76, bgcolor: white"]0:00
[/TD]
[TD="class: xl77, bgcolor: white"]0:45
[/TD]
[TD="class: xl77, bgcolor: white"]0
[/TD]
[TD="class: xl78, bgcolor: white"]0
[/TD]
[TD="class: xl79, width: 69, bgcolor: transparent"]0:00
[/TD]
[TD="class: xl80, width: 64, bgcolor: transparent"]0
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl81, width: 88, bgcolor: transparent"]TUESDAY
[/TD]
[TD="class: xl82, width: 89, bgcolor: transparent"]29/01/2013
[/TD]
[TD="class: xl83, width: 87, bgcolor: transparent"]DIGI
[/TD]
[TD="class: xl83, width: 173, bgcolor: transparent"][/TD]
[TD="class: xl84, width: 78, bgcolor: transparent"][/TD]
[TD="class: xl84, width: 77, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: yellow"]0:00
[/TD]
[TD="class: xl74, bgcolor: yellow"]0
[/TD]
[TD="class: xl75, bgcolor: yellow"]0.00
[/TD]
[TD="class: xl74, bgcolor: yellow"]0:00
[/TD]
[TD="class: xl74, bgcolor: yellow"]0:00
[/TD]
[TD="class: xl74, bgcolor: yellow"]0:00
[/TD]
[TD="class: xl75, bgcolor: yellow"]0.00
[/TD]
[TD="class: xl75, bgcolor: yellow"][/TD]
[TD="class: xl75, bgcolor: yellow"]45.00
[/TD]
[TD="class: xl76, bgcolor: white"]0:00
[/TD]
[TD="class: xl77, bgcolor: white"]0:45
[/TD]
[TD="class: xl77, bgcolor: white"]0
[/TD]
[TD="class: xl78, bgcolor: white"]0
[/TD]
[TD="class: xl79, width: 69, bgcolor: transparent"]0:00
[/TD]
[TD="class: xl80, width: 64, bgcolor: transparent"]0
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl93, bgcolor: transparent, colspan: 2"]£0.00
[/TD]
[/TR]
[TR]
[TD="class: xl81, width: 88, bgcolor: transparent"]WEDNESDAY
[/TD]
[TD="class: xl82, width: 89, bgcolor: transparent"]30/01/2013
[/TD]
[TD="class: xl83, width: 87, bgcolor: transparent"]DIGI
[/TD]
[TD="class: xl83, width: 173, bgcolor: transparent"][/TD]
[TD="class: xl84, width: 78, bgcolor: transparent"][/TD]
[TD="class: xl84, width: 77, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: yellow"]0:00
[/TD]
[TD="class: xl74, bgcolor: yellow"]0
[/TD]
[TD="class: xl75, bgcolor: yellow"]0.00
[/TD]
[TD="class: xl74, bgcolor: yellow"]0:00
[/TD]
[TD="class: xl74, bgcolor: yellow"]0:00
[/TD]
[TD="class: xl74, bgcolor: yellow"]0:00
[/TD]
[TD="class: xl75, bgcolor: yellow"]0.00
[/TD]
[TD="class: xl75, bgcolor: yellow"][/TD]
[TD="class: xl75, bgcolor: yellow"]45.00
[/TD]
[TD="class: xl76, bgcolor: white"]0:00
[/TD]
[TD="class: xl77, bgcolor: white"]0:45
[/TD]
[TD="class: xl77, bgcolor: white"]0
[/TD]
[TD="class: xl78, bgcolor: white"]0
[/TD]
[TD="class: xl79, width: 69, bgcolor: transparent"]0:00
[/TD]
[TD="class: xl80, width: 64, bgcolor: transparent"]0
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl81, width: 88, bgcolor: transparent"]THURSDAY
[/TD]
[TD="class: xl82, width: 89, bgcolor: transparent"]31/01/2013
[/TD]
[TD="class: xl83, width: 87, bgcolor: transparent"]DIGI
[/TD]
[TD="class: xl83, width: 173, bgcolor: transparent"][/TD]
[TD="class: xl84, width: 78, bgcolor: transparent"][/TD]
[TD="class: xl84, width: 77, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: yellow"]0:00
[/TD]
[TD="class: xl74, bgcolor: yellow"]0
[/TD]
[TD="class: xl75, bgcolor: yellow"]0.00
[/TD]
[TD="class: xl74, bgcolor: yellow"]0:00
[/TD]
[TD="class: xl74, bgcolor: yellow"]0:00
[/TD]
[TD="class: xl74, bgcolor: yellow"]0:00
[/TD]
[TD="class: xl75, bgcolor: yellow"]0.00
[/TD]
[TD="class: xl75, bgcolor: yellow"][/TD]
[TD="class: xl75, bgcolor: yellow"]45.00
[/TD]
[TD="class: xl76, bgcolor: white"]0:00
[/TD]
[TD="class: xl77, bgcolor: white"]0:45
[/TD]
[TD="class: xl77, bgcolor: white"]0
[/TD]
[TD="class: xl78, bgcolor: white"]0
[/TD]
[TD="class: xl79, width: 69, bgcolor: transparent"]0:00
[/TD]
[TD="class: xl80, width: 64, bgcolor: transparent"]0
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl81, width: 88, bgcolor: transparent"]FRIDAY
[/TD]
[TD="class: xl82, width: 89, bgcolor: transparent"]01/02/2013
[/TD]
[TD="class: xl83, width: 87, bgcolor: transparent"]DIGI
[/TD]
[TD="class: xl83, width: 173, bgcolor: transparent"][/TD]
[TD="class: xl84, width: 78, bgcolor: transparent"][/TD]
[TD="class: xl84, width: 77, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: yellow"]0:00
[/TD]
[TD="class: xl74, bgcolor: yellow"]0
[/TD]
[TD="class: xl75, bgcolor: yellow"]0.00
[/TD]
[TD="class: xl74, bgcolor: yellow"]0:00
[/TD]
[TD="class: xl74, bgcolor: yellow"]0:00
[/TD]
[TD="class: xl74, bgcolor: yellow"]0:00
[/TD]
[TD="class: xl75, bgcolor: yellow"]0.00
[/TD]
[TD="class: xl75, bgcolor: yellow"][/TD]
[TD="class: xl75, bgcolor: yellow"]45.00
[/TD]
[TD="class: xl76, bgcolor: white"]0:00
[/TD]
[TD="class: xl77, bgcolor: white"]0:45
[/TD]
[TD="class: xl77, bgcolor: white"]0
[/TD]
[TD="class: xl78, bgcolor: white"]0
[/TD]
[TD="class: xl79, width: 69, bgcolor: transparent"]0:00
[/TD]
[TD="class: xl80, width: 64, bgcolor: transparent"]0
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, colspan: 2"]STD Hours
[/TD]
[/TR]
[TR]
[TD="class: xl85, width: 88, bgcolor: transparent"]SATURDAY
[/TD]
[TD="class: xl86, width: 89, bgcolor: transparent"]02/02/2013
[/TD]
[TD="class: xl87, width: 87, bgcolor: transparent"]DIGI
[/TD]
[TD="class: xl87, width: 173, bgcolor: transparent"][/TD]
[TD="class: xl88, width: 78, bgcolor: transparent"][/TD]
[TD="class: xl88, width: 77, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: yellow"]0:00
[/TD]
[TD="class: xl74, bgcolor: yellow"]0
[/TD]
[TD="class: xl75, bgcolor: yellow"]0.00
[/TD]
[TD="class: xl74, bgcolor: yellow"]0:00
[/TD]
[TD="class: xl74, bgcolor: yellow"]0:00
[/TD]
[TD="class: xl74, bgcolor: yellow"]0:00
[/TD]
[TD="class: xl75, bgcolor: yellow"]0.00
[/TD]
[TD="class: xl75, bgcolor: yellow"][/TD]
[TD="class: xl75, bgcolor: yellow"]45.00
[/TD]
[TD="class: xl76, bgcolor: white"]0:00
[/TD]
[TD="class: xl77, bgcolor: white"]0:45
[/TD]
[TD="class: xl77, bgcolor: white"]0
[/TD]
[TD="class: xl78, bgcolor: white"]0
[/TD]
[TD="class: xl79, width: 69, bgcolor: transparent"]0:00
[/TD]
[TD="class: xl80, width: 64, bgcolor: transparent"]0
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, colspan: 2"]Night Allowance
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, colspan: 2"]O/T Hours
[/TD]
[/TR]
[TR]
[TD="class: xl97, width: 264, bgcolor: transparent, colspan: 3"]TOTAL WEEKLY DUTY HOURS MINUS BREAKS TAKEN
[/TD]
[TD="class: xl89, bgcolor: transparent"]0:00
[/TD]
[TD="class: xl97, width: 939, bgcolor: transparent, colspan: 12"]TOTAL WEEKLY WTD HOURS DECLARED
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl98, bgcolor: transparent, colspan: 2"]0:00
[/TD]
[TD="class: xl90, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]
 
Sort by date Sort by votes
The red cell above can then be moved into Paid duty time if you wanted this to determine a minimum pay of 8 hours, otherwise whatever the hours are above that.
 
Upvote 0

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Your formula works but it also gives an 8:00 result if no times are entered where as it should give a 0 result if i dont work at all that day
 
Upvote 0
Edit.

It's playing up and keeps deleting the formula........2 mins
 
Last edited:
Upvote 0
Excel 2010
Y

<colgroup><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]10[/TD]
[TD="align: right"]0.46875[/TD]

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: #DAE7F5"]Y10[/TH]
[TD="align: left"]=IF(OR(V8=0,V8=""),0,IF(V8<W8,W8,IF(V8-S8>0,V8-S8,0)))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Ignore the 0.46875...........it just hasn't been formatted as time
 
Upvote 0
it is on the right track but i need it to ignore the 45 min break if it is less than 8 hours
Example 05:00 - 17:00 11:15
Example 05:00 - 12:00 8:00
 
Upvote 0
Take out the " " in this formula that are around the lower or higher symbols.......The forum mistakes them for HTML code and deletes half the formula!

=IF(OR(V8=0,V8=""),0,IF(V8"<"W8,W8,IF(V8-S8">"0,IF(V8-S8"<"W10,W10,V8-S8),0)))
 
Upvote 0
This formula works fine for the Paid duty time so Great
Problem now is Std Rate hours are from 6:00 till 18:00 outside of this time is overtime. my spreadsheet works out the split for 05:00 to 12:45 as 6:00 standard and 1 hour O/time it should be 7:00 at std hours + 1 hour O/Time
The formula i have is fine for anyting above 8 hours
g9= =MEDIAN("06:00","18:00",IF(L9<K9,1+L9,L9))-MEDIAN("06:00","18:00",K9)
h9= =IF(G9-Q9>0,G9-Q9,"0")
i9= =(H9*24)*11.25
j9= =MOD(L9-K9,1)-G9
m9= =(J9*24)*12.5
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
TIME LIMITATIONS
Replies
1
Views
72
cooleomter
C
A
  • Question Question
Timesheet
Replies
3
Views
94
etaf
etaf
T
  • Question Question
VBA help to count Daytime,Nighttime and Summary
2 3
Replies
20
Views
841
Alex Blakenburg
A
C
  • Question Question
Ordinary Hours or Penalty Shift
Replies
0
Views
414
ChetanPuri
C
M
  • Question Question
Pivot table not showing as Numbers
Replies
1
Views
274
shift-del
shift-del

Forum statistics

Threads
1,223,961
Messages
6,175,652
Members
452,664
Latest member
alpserbetli

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