#1   Report Post  
Posted to microsoft.public.excel.misc
Monty
 
Posts: n/a
Default hourly rates

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
  #2   Report Post  
Posted to microsoft.public.excel.misc
Bernard Liengme
 
Posts: n/a
Default hourly rates

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

"Monty" wrote in message
...
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



  #3   Report Post  
Posted to microsoft.public.excel.misc
Toppers
 
Posts: n/a
Default hourly rates

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
"Monty" wrote:

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

  #4   Report Post  
Posted to microsoft.public.excel.misc
Monty
 
Posts: n/a
Default hourly rates

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


"Bernard Liengme" wrote:

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

"Monty" wrote in message
...
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




  #5   Report Post  
Posted to microsoft.public.excel.misc
Toppers
 
Posts: n/a
Default hourly rates

Format column A as hh:mm.

"Monty" wrote:

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


"Bernard Liengme" wrote:

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

"Monty" wrote in message
...
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






  #6   Report Post  
Posted to microsoft.public.excel.misc
Bernard Liengme
 
Posts: n/a
Default hourly rates

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

"Monty" wrote in message
...
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


"Bernard Liengme" wrote:

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

"Monty" wrote in message
...
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






  #7   Report Post  
Posted to microsoft.public.excel.misc
Monty
 
Posts: n/a
Default hourly rates

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

"Bernard Liengme" wrote:

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

"Monty" wrote in message
...
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




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I multiply an hourly cost by hours expressed in minutes? Enrique Excel Worksheet Functions 3 February 16th 06 03:35 PM
Calculation of hourly rate times hours times 1.5 Newbusinessbod Excel Worksheet Functions 1 December 6th 05 05:44 PM
Hourly rates Dreamstar_1961 Excel Worksheet Functions 3 November 2nd 05 12:33 PM
salary, hourly wage plus commision Excel for payroll Charts and Charting in Excel 0 October 17th 05 05:07 AM
Trying to develop a shipping calculator to compare courier rates Crys Excel Discussion (Misc queries) 2 May 9th 05 09:21 PM


All times are GMT +1. The time now is 01:44 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"