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

[[email protected]]

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
news:[email protected]...
>
> 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
>

[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,
Jesse

[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