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


--
Jesse_Norris
------------------------------------------------------------------------
Jesse_Norris's Profile: http://www.excelforum.com/member.php...o&userid=25546
View this thread: http://www.excelforum.com/showthread...hreadid=401382

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

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"
wrote in message
news:Jesse_Norris.1uptua_1125637503.3685@excelforu m-nospam.com...

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


--
Jesse_Norris
------------------------------------------------------------------------
Jesse_Norris's Profile:
http://www.excelforum.com/member.php...o&userid=25546
View this thread: http://www.excelforum.com/showthread...hreadid=401382

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


--
Jesse_Norris
------------------------------------------------------------------------
Jesse_Norris's Profile: http://www.excelforum.com/member.php...o&userid=25546
View this thread: http://www.excelforum.com/showthread...hreadid=401382

Wrote:
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 |
+-------------------------------------------------------------------+

--
Jesse_Norris
------------------------------------------------------------------------
Jesse_Norris's Profile: http://www.excelforum.com/member.php...o&userid=25546
View this thread: http://www.excelforum.com/showthread...hreadid=401382