time sheet

[Jesse_Norris]

Hi all

My payroll officer has pointed out to me that the time sheet i have
created is good but i am loosing out on cash because the way they work
out the hours is different to the way my time sheet dose, on my time
sheet if i work for 7 H and 40 Mins it
Displays as 7:40. But here is where i lose out as when the payroll see
7:40 they take it as 7H 24Mins. Dose anyone have a suggestion as to how
i can get 7:40 to display the correct time (7:67) with out starting from
scratch?


the payroll work on the following setup
1min= .02
10mins= .17
15mins= .25
20mins= .33
30mins= .50
40mins= .67
etc.

these are my current formulas

cacluates lunch period
=E10-D10
total time worked less total lunch
=((F10-C10)-G10)
total OT for the day
=J10-I10
Total Time Worked + OT =SUM(H10:H23)+K24 (K24 is the cell containing
the total of the over time)

this time sheet covers a F/N

Thanks in advance
Regards,
Jesse

 

[writetodaveo]

Hi Jesse,

You need to multiply the time by 24 to turn it into a decimal.

e.g. Total Time Worked + OT =(SUM(H10:H23)+K24)*24

7:40 becomes 7.67

Make sure you format the cells to number with 2dp.

HTH - David

 

[Biff]

Hi!

7:40 they take it as 7H 24Mins

Huh? That makes no sense to me!

how i can get 7:40 to display the correct time (7:67) with out starting
from scratch?

You are already displaying the correct time. 7:67 is not correct!

What you probably want to do is, instead of displaying the times in h:mm
format, display the times in decimal format: 7.67

To do that, just add a multiplication operation to your formulas:

cacluates lunch period
=E10-D10
=(E10-D10)*24

total time worked less total lunch
=((F10-C10)-G10)
=((F10-C10)-G10)*24

total OT for the day
=J10-I10
=(J10-I10)*24

Total Time Worked + OT =SUM(H10:H23)+K24

=SUM(H10:H23,K24)*24

Format all of these cells as GENERAL

Biff

"Jesse_Norris" <[email protected]>
wrote in message

 

[Jesse_Norris]

Thanks Guys
i have been away for a week so sorry i havent responded till now
i will give it ago now.
Regards,
Jess

 

[Jesse_Norris]

Hi Jesse,

You need to multiply the time by 24 to turn it into a decimal.

e.g. Total Time Worked + OT =(SUM(H10:H23)+K24)*24

7:40 becomes 7.67

Make sure you format the cells to number with 2dp.

HTH - David

david it all works execpt when you get to the total below is the
example i used to test :
Time Worked (hrs)

sat 0.00 =((F10-C10)-G10)*24
sun 0.00
mon 1.00
tue 7.67
wed 7.25
thurs7.75
fri 0.00
sat 0.00
sun 0.00
mon 0.00
tue0.00
wed7.67
thurs7.50
fri 7.33 =((F23-C23)-G23)*24

Total Time Worked +toil taken 1108.00 =(SUM(H10:H23)+K24)*24
this includes no toil.
i have added a text version TD for you to look at for referance
Regards,
Jesse


+-------------------------------------------------------------------+
|Filename: 200805.txt |
|Download: http://www.excelforum.com/attachment.php?postid=3787 |
+-------------------------------------------------------------------+