Home 
Search 
Today's Posts 
#1




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




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




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




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




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




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




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 
Display Modes  


Similar Threads  
Thread  Forum  
How do I multiply an hourly cost by hours expressed in minutes?  Excel Worksheet Functions  
Calculation of hourly rate times hours times 1.5  Excel Worksheet Functions  
Hourly rates  Excel Worksheet Functions  
salary, hourly wage plus commision  Charts and Charting in Excel  
Trying to develop a shipping calculator to compare courier rates  Excel Discussion (Misc queries) 