hourly rates

M

Monty

i would like to have in cell A1 the number of overtime hrs worked per week
for one member of staff which in this case is 12hrs 26mins. in cell b1 i
have the hourly rate £7.04, in cell c1 i have the following=A1*B1*2 this
should bring back £175.02 however it brings back £172.62. it would appear
that it is not picking up the mins.
does anyone have any suggestions.

Thanks

Monty
 
B

Bernard Liengme

To get this result (=A1*B1*2 giving 175.02 when B1 =7.04) the value in A1
must be 12.26
This is not 12hr 26 mins but 12hrs plus 26/100th of an hour (about 15 mins)

What you need in A1 is 12:26 (the colon makes this a time value)
And since time is stored as fraction of a day you now need in C1 the formula
=A1*24*B1*2 where the 24 converts the fraction of a day to hours. This gives
172.62 - you may need to format the C1.

best wishes
 
T

Toppers

Assuming A1 is formatted as HH:MM then in C1:

=A1*24*B1*2 (to convert the time to decimal portion of a day - 24 hours
=1.000, 12hrs 26 mins =0.5180555556)


=£175.06 (I believe!)


HTH
 
M

Monty

this works great, however as i have a lot of calculations of overtime to do
it will take a lot of time.
can i set up each cell in column A to have the colon : already there or i
have created a combo box with the hours 0:01 to 24:00, this works great for
the calculation hover after you highlght the hours the figures all change to
decimals i have looked in the properties box for some kind of adjustment but
have had no joy.

any suggestions

once again thanks.

monty
 
T

Toppers

Format column A as hh:mm.

Monty said:
this works great, however as i have a lot of calculations of overtime to do
it will take a lot of time.
can i set up each cell in column A to have the colon : already there or i
have created a combo box with the hours 0:01 to 24:00, this works great for
the calculation hover after you highlght the hours the figures all change to
decimals i have looked in the properties box for some kind of adjustment but
have had no joy.

any suggestions

once again thanks.

monty
Click to expand...
 
M

Monty

Cheers this worked great.
can i ask one moe question. if i have cell A1 with 12.26 and cell A2 with
08.04 and A3 with 20.33, how can i get this to add up to hours ie 41.03 to
work with your previous solution.

Thanks

Monty
 
Top