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>

 

[ca.moazzam]

Hi email me at ca.moazzam (gmail) with any additional comments if these could help.

Moazzam

 

[ChrisR]

Hi,

Check your inbox............Can you either post up some sample data and what you expect to see?

 

[HappyChappy]

http://dl.dropbox.com/u/13363263/Book1.xlsx

Here is a link to the spreadsheet if it helps

 

[ChrisR]

Hi,

Is it this in cell S8?

=IF(T8<8,8,IF(T8-Q8>0,T8-Q8,0))

Regards,
Chris

 

[HappyChappy]

=IF(T10<8,8,IF(T10-Q10>0,T10-Q10,0))

this is what i have in S8 tried your formula and it gives me the result 0:00

 

[ChrisR]

Why does it say T10/Q10 etc if you have it in row 8? It should be T8 Q8 etc?

 

[HappyChappy]

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

05:00

17:00

11:00

10:15

115.31

1:00

5:00

17:00

12.50

45.00

0:00

0:45

11:15

11:15

12:00

£127.81

TUESDAY

29/01/2013

DIGI

05:00

13:00

7:00

7:00

78.75

1:00

5:00

13:00

12.50

45.00

0:00

0:45

8:00

8:00

8:00

£91.25

WEDNESDAY

30/01/2013

DIGI

05:00

13:45

7:45

7:00

78.75

1:00

5:00

13:45

12.50

45.00

0:00

0:45

8:00

8:00

8:45

£91.25

THURSDAY

31/01/2013

DIGI

05:00

14:45

8:45

8:00

90.00

1:00

5:00

14:45

12.50

45.00

0:00

0:45

9:00

9:00

9:45

£102.50

<colgroup><col style="width: 66pt; mso-width-source: userset; mso-width-alt: 3218;" width="88"> <col style="width: 67pt; mso-width-source: userset; mso-width-alt: 3254;" width="89"> <col style="width: 65pt; mso-width-source: userset; mso-width-alt: 3181;" width="87"> <col style="width: 130pt; mso-width-source: userset; mso-width-alt: 6326;" width="173"> <col style="width: 59pt; mso-width-source: userset; mso-width-alt: 2852;" width="78"> <col style="width: 58pt; mso-width-source: userset; mso-width-alt: 2816;" width="77"> <col style="width: 78pt; mso-width-source: userset; mso-width-alt: 3803;" width="104"> <col style="width: 58pt; mso-width-source: userset; mso-width-alt: 2816;" span="8" width="77"> <col style="width: 48pt;" span="2" width="64"> <col style="width: 64pt; mso-width-source: userset; mso-width-alt: 3108;" width="85"> <col style="width: 80pt; mso-width-source: userset; mso-width-alt: 3913;" width="107"> <col style="width: 52pt; mso-width-source: userset; mso-width-alt: 2523;" width="69"> <col style="width: 48pt; mso-width-source: userset; mso-width-alt: 2340;" width="64"> <tbody>
</tbody>

Some sample data entered

 

[HappyChappy]

Sorry i copied the wrong cell it should read 8 not 10's

 

[ChrisR]

Are you saying it doesn't work?

If so, it's due to the numbers are formatted as time I think............I've just tested it and this works:-

=IF(V8<W8,W8,IF(V8-S8>0,V8-S8,0))

In column W I have added a column saying minimum time and then 08:00 in each row

Here is my data

Excel 2010
	R	S	T	U	V	W	X	Y
3
4
5
6	POA	Break	Total WTD Time	Paid Duty Time	Clocked hours	MINIMUM HOURS
7	0:00	0:00	0	0	0:00	8:00
8	0:00	0:45	11:15	11:15	12:00	8:00		11:15
9	0:00	0:45	0	0	0:00	8:00
10	0:00	0:45	0	0	0:00	8:00
11	0:00	0:45	0	0	0:00	8:00
12	0:00	0:45	0	0	0:00	8:00
13	0:00	0:45	0	0	0:00	8:00
Sheet1

Cell Formulas
Range	Formula
Y8	=IF(V8V8-S8>0,V8-S8,0))
W8	=W7
W9	=W8
W10	=W9
W11	=W10
W12	=W11
W13	=W12

I have just put the formula in the red cell at the moment. Which is

=IF(V8<W8,W8,IF(V8-S8>0,V8-S8,0))

Make sure this is also formatted to time, like the rest of the cells. I have also amended the formula to pick up a different break column, as the one you were using was in a different format.

Try this.........