software

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

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
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

quot;Montyquot; gt; wrote in message
...
gt;i would like to have in cell A1 the number of overtime hrs worked per week
gt; for one member of staff which in this case is 12hrs 26mins. in cell b1 i
gt; have the hourly rate ?7.04, in cell c1 i have the following=A1*B1*2 this
gt; should bring back ?175.02 however it brings back ?172.62. it would appear
gt; that it is not picking up the mins.
gt; does anyone have any suggestions.
gt;
gt; Thanks
gt;
gt; Monty
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
quot;Montyquot; wrote:

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

montyquot;Bernard Liengmequot; wrote:

gt; To get this result (=A1*B1*2 giving 175.02 when B1 =7.04) the value in A1
gt; must be 12.26
gt; This is not 12hr 26 mins but 12hrs plus 26/100th of an hour (about 15 mins)
gt;
gt; What you need in A1 is 12:26 (the colon makes this a time value)
gt; And since time is stored as fraction of a day you now need in C1 the formula
gt; =A1*24*B1*2 where the 24 converts the fraction of a day to hours. This gives
gt; 172.62 - you may need to format the C1.
gt;
gt; best wishes
gt; --
gt; Bernard V Liengme
gt; www.stfx.ca/people/bliengme
gt; remove caps from email
gt;
gt; quot;Montyquot; gt; wrote in message
gt; ...
gt; gt;i would like to have in cell A1 the number of overtime hrs worked per week
gt; gt; for one member of staff which in this case is 12hrs 26mins. in cell b1 i
gt; gt; have the hourly rate 拢7.04, in cell c1 i have the following=A1*B1*2 this
gt; gt; should bring back 拢175.02 however it brings back 拢172.62. it would appear
gt; gt; that it is not picking up the mins.
gt; gt; does anyone have any suggestions.
gt; gt;
gt; gt; Thanks
gt; gt;
gt; gt; Monty
gt;
gt;
gt;

Format column A as hh:mm.

quot;Montyquot; wrote:

gt; this works great, however as i have a lot of calculations of overtime to do
gt; it will take a lot of time.
gt; can i set up each cell in column A to have the colon : already there or i
gt; have created a combo box with the hours 0:01 to 24:00, this works great for
gt; the calculation hover after you highlght the hours the figures all change to
gt; decimals i have looked in the properties box for some kind of adjustment but
gt; have had no joy.
gt;
gt; any suggestions
gt;
gt; once again thanks.
gt;
gt; monty
gt;
gt;
gt; quot;Bernard Liengmequot; wrote:
gt;
gt; gt; To get this result (=A1*B1*2 giving 175.02 when B1 =7.04) the value in A1
gt; gt; must be 12.26
gt; gt; This is not 12hr 26 mins but 12hrs plus 26/100th of an hour (about 15 mins)
gt; gt;
gt; gt; What you need in A1 is 12:26 (the colon makes this a time value)
gt; gt; And since time is stored as fraction of a day you now need in C1 the formula
gt; gt; =A1*24*B1*2 where the 24 converts the fraction of a day to hours. This gives
gt; gt; 172.62 - you may need to format the C1.
gt; gt;
gt; gt; best wishes
gt; gt; --
gt; gt; Bernard V Liengme
gt; gt; www.stfx.ca/people/bliengme
gt; gt; remove caps from email
gt; gt;
gt; gt; quot;Montyquot; gt; wrote in message
gt; gt; ...
gt; gt; gt;i would like to have in cell A1 the number of overtime hrs worked per week
gt; gt; gt; for one member of staff which in this case is 12hrs 26mins. in cell b1 i
gt; gt; gt; have the hourly rate 拢7.04, in cell c1 i have the following=A1*B1*2 this
gt; gt; gt; should bring back 拢175.02 however it brings back 拢172.62. it would appear
gt; gt; gt; that it is not picking up the mins.
gt; gt; gt; does anyone have any suggestions.
gt; gt; gt;
gt; gt; gt; Thanks
gt; gt; gt;
gt; gt; gt; Monty
gt; gt;
gt; gt;
gt; gt;

Use this
=TIME(INT(A1),MOD(A1,1)*100,0)*24*B1*2

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

quot;Montyquot; gt; wrote in message
...
gt; this works great, however as i have a lot of calculations of overtime to
gt; do
gt; it will take a lot of time.
gt; can i set up each cell in column A to have the colon : already there or i
gt; have created a combo box with the hours 0:01 to 24:00, this works great
gt; for
gt; the calculation hover after you highlght the hours the figures all change
gt; to
gt; decimals i have looked in the properties box for some kind of adjustment
gt; but
gt; have had no joy.
gt;
gt; any suggestions
gt;
gt; once again thanks.
gt;
gt; monty
gt;
gt;
gt; quot;Bernard Liengmequot; wrote:
gt;
gt;gt; To get this result (=A1*B1*2 giving 175.02 when B1 =7.04) the value in A1
gt;gt; must be 12.26
gt;gt; This is not 12hr 26 mins but 12hrs plus 26/100th of an hour (about 15
gt;gt; mins)
gt;gt;
gt;gt; What you need in A1 is 12:26 (the colon makes this a time value)
gt;gt; And since time is stored as fraction of a day you now need in C1 the
gt;gt; formula
gt;gt; =A1*24*B1*2 where the 24 converts the fraction of a day to hours. This
gt;gt; gives
gt;gt; 172.62 - you may need to format the C1.
gt;gt;
gt;gt; best wishes
gt;gt; --
gt;gt; Bernard V Liengme
gt;gt; www.stfx.ca/people/bliengme
gt;gt; remove caps from email
gt;gt;
gt;gt; quot;Montyquot; gt; wrote in message
gt;gt; ...
gt;gt; gt;i would like to have in cell A1 the number of overtime hrs worked per
gt;gt; gt;week
gt;gt; gt; for one member of staff which in this case is 12hrs 26mins. in cell b1
gt;gt; gt; i
gt;gt; gt; have the hourly rate ?7.04, in cell c1 i have the following=A1*B1*2
gt;gt; gt; this
gt;gt; gt; should bring back ?175.02 however it brings back ?172.62. it would
gt;gt; gt; appear
gt;gt; gt; that it is not picking up the mins.
gt;gt; gt; does anyone have any suggestions.
gt;gt; gt;
gt;gt; gt; Thanks
gt;gt; gt;
gt;gt; gt; Monty
gt;gt;
gt;gt;
gt;gt;
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

quot;Bernard Liengmequot; wrote:

gt; To get this result (=A1*B1*2 giving 175.02 when B1 =7.04) the value in A1
gt; must be 12.26
gt; This is not 12hr 26 mins but 12hrs plus 26/100th of an hour (about 15 mins)
gt;
gt; What you need in A1 is 12:26 (the colon makes this a time value)
gt; And since time is stored as fraction of a day you now need in C1 the formula
gt; =A1*24*B1*2 where the 24 converts the fraction of a day to hours. This gives
gt; 172.62 - you may need to format the C1.
gt;
gt; best wishes
gt; --
gt; Bernard V Liengme
gt; www.stfx.ca/people/bliengme
gt; remove caps from email
gt;
gt; quot;Montyquot; gt; wrote in message
gt; ...
gt; gt;i would like to have in cell A1 the number of overtime hrs worked per week
gt; gt; for one member of staff which in this case is 12hrs 26mins. in cell b1 i
gt; gt; have the hourly rate 拢7.04, in cell c1 i have the following=A1*B1*2 this
gt; gt; should bring back 拢175.02 however it brings back 拢172.62. it would appear
gt; gt; that it is not picking up the mins.
gt; gt; does anyone have any suggestions.
gt; gt;
gt; gt; Thanks
gt; gt;
gt; gt; Monty
gt;
gt;
gt;