Timesheet GURU needed

[HappyChappy]

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.

WEEKLY ACCOUNTABILITY/DISCLAIMER FOR HOURS/SHIFTS WORKED

DRIVERS NAME

Mark Thomas

W/C

27/01/2013

WTD YOUR DUTY TIME MINUS POA BREAKS PAID TIME YOUR DUTY TIME MINUS BREAKS POA MUST BE DECLARED OVER 15 MINUTE BLOCKS

Working Day

Date

TACHO Type

Client Site

Start

Finish

Std Rate Hours Before Break

Std Rate Hours

Std Total

O/T Rate Hours

Round to 15 Min

Round to 15 Min

O/T Total

POA don't print

Break don't print

POA

Break

Total WTD Time

Paid Duty Time

Clocked Hours

Daily Total

SUNDAY

27/01/2013

0:00

0

0.00

0:00

0:00

0:00

0.00

0:00

0:00

0

0

0:00

0

MONDAY

28/01/2013

DIGI

0:00

0

0.00

0:00

0:00

0:00

0.00

45.00

0:00

0:45

0

0

0:00

0

TUESDAY

29/01/2013

DIGI

0:00

0

0.00

0:00

0:00

0:00

0.00

45.00

0:00

0:45

0

0

0:00

0

£0.00

WEDNESDAY

30/01/2013

DIGI

0:00

0

0.00

0:00

0:00

0:00

0.00

45.00

0:00

0:45

0

0

0:00

0

THURSDAY

31/01/2013

DIGI

0:00

0

0.00

0:00

0:00

0:00

0.00

45.00

0:00

0:45

0

0

0:00

0

FRIDAY

01/02/2013

DIGI

0:00

0

0.00

0:00

0:00

0:00

0.00

45.00

0:00

0:45

0

0

0:00

0

STD Hours

SATURDAY

02/02/2013

DIGI

0:00

0

0.00

0:00

0:00

0:00

0.00

45.00

0:00

0:45

0

0

0:00

0

Night Allowance

O/T Hours

TOTAL WEEKLY DUTY HOURS MINUS BREAKS TAKEN

0:00

TOTAL WEEKLY WTD HOURS DECLARED

0:00

<tbody>
</tbody>

 

[ChrisR]

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.

 

[HappyChappy]

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

 

[ChrisR]

Edit.

It's playing up and keeps deleting the formula........2 mins

 

[ChrisR]

Excel 2010

	Y
10	0.46875

<colgroup><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula Y10 =IF(OR(V8=0,V8=""),0,IF(V8<W8,W8,IF(V8-S8>0,V8-S8,0))) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Ignore the 0.46875...........it just hasn't been formatted as time

 

[HappyChappy]

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

 

[ChrisR]

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)))

 

[ca.moazzam]

I will get in to this one after some time if found it still unresolved.

 

[HappyChappy]

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

 

[HappyChappy]

Updated the spreadsheet with the new formulas and posted on my dropbox only the one formula to tweak now i hope take a look here is the link

https://www.dropbox.com/sh/pd7n9froxauyw91/qervYgBvVD