How can I convert hours worked to a number to multiply an hourly r

[Guest]

I have a spreadsheet to enter my employees hours. I have to convert the
minutes into a number to multiply by the hourly rate. Currently I have a
column with the hours the one with the minutes and a one to recalculate the
minutes.
Example A1=37 hours A2=17 minutes A3=A2/60 A4=A1+A3
A4 is linked to another spreadsheet that multiplies this by the hourly rate.
About 7 out of 10 are usually correct but the other three may be off by 2 to
6 cents. Is there another way to set this up to be accurate? I have tried
to extend the number to many decimal points and made sure the cell is not
rounding but I still have the same problem. Thanks to anyone that can help
me.

 

[Ron Rosenfeld]

On Mon, 20 Sep 2004 16:15:17 -0700, Patte from Florida <Patte from

I have a spreadsheet to enter my employees hours. I have to convert the
minutes into a number to multiply by the hourly rate. Currently I have a
column with the hours the one with the minutes and a one to recalculate the
minutes.
Example A1=37 hours A2=17 minutes A3=A2/60 A4=A1+A3
A4 is linked to another spreadsheet that multiplies this by the hourly rate.
About 7 out of 10 are usually correct but the other three may be off by 2 to
6 cents. Is there another way to set this up to be accurate? I have tried
to extend the number to many decimal points and made sure the cell is not
rounding but I still have the same problem. Thanks to anyone that can help
me.

1. Post some real data with the actual result and the expected result.
2. Post the formula you are using that is off by 2 to 6 cents

Suggestion:
3. If you enter the times in as Excel times -- for example, if you had in A1
37:13 (format this as [h]:mm), then in A4 you could have A1*24 to convert the
Excel time to a decimal time that can be multiplied by the hourly rate.


--ron

 

[Guest]

Thanks, Ron. I tried your suggestion and it worked out to the same number
that I had. If I use that cell and multiply it by the hourly wage on the
same spreadsheet it comes out right however when it is linked to another it
was over 3 cents. these are the figures on the first spreadsheet
I29 37:23 K29 =I19*24
37.38
on the second one I have
C4 =[we081404.xls]Sheet1!$K$29 D4 $7.50 E4 =SUM(C4*D4)
37.38
280.38 should be 280.35
I have C4 and E4 formatted as a number cell and D4 as a currency cell
I think there must be something I'm missing between the two spreadsheets.
I've asked others who use excel more that I do and they said this happens to
them but I'm sure there must be a way to make it correct.
Thanks again. Patte

On Mon, 20 Sep 2004 16:15:17 -0700, Patte from Florida <Patte from

I have a spreadsheet to enter my employees hours. I have to convert the
minutes into a number to multiply by the hourly rate. Currently I have a
column with the hours the one with the minutes and a one to recalculate the
minutes.
Example A1=37 hours A2=17 minutes A3=A2/60 A4=A1+A3
A4 is linked to another spreadsheet that multiplies this by the hourly rate.
About 7 out of 10 are usually correct but the other three may be off by 2 to
6 cents. Is there another way to set this up to be accurate? I have tried
to extend the number to many decimal points and made sure the cell is not
rounding but I still have the same problem. Thanks to anyone that can help
me.

1. Post some real data with the actual result and the expected result.
2. Post the formula you are using that is off by 2 to 6 cents

Suggestion:
3. If you enter the times in as Excel times -- for example, if you had in A1
37:13 (format this as [h]:mm), then in A4 you could have A1*24 to convert the
Excel time to a decimal time that can be multiplied by the hourly rate.


--ron

 

[Ron Rosenfeld]

Thanks, Ron. I tried your suggestion and it worked out to the same number
that I had. If I use that cell and multiply it by the hourly wage on the
same spreadsheet it comes out right however when it is linked to another it
was over 3 cents. these are the figures on the first spreadsheet
I29 37:23 K29 =I19*24
37.38
on the second one I have
C4 =[we081404.xls]Sheet1!$K$29 D4 $7.50 E4 =SUM(C4*D4)
37.38
280.38 should be 280.35
I have C4 and E4 formatted as a number cell and D4 as a currency cell
I think there must be something I'm missing between the two spreadsheets.
I've asked others who use excel more that I do and they said this happens to
them but I'm sure there must be a way to make it correct.
Thanks again. Patte

Patte,

Excel is calculating correctly.

The reason you get $280.38 is because you are not multiplying 37.38 * 7.5.

You are actually multiplying 37.383333333333... * 7.5 which gives 280.375

37 hrs 23 minutes is not exactly 37.38. 23/60 = 0.3833333333... + 37 =
37.383333333

So the 'correct' answer is, in fact, $280.38 (rounded to the nearest cent).

=================

I suspect other numbers which have been classified as irregular can be
explained by the same phenomenon.


--ron

 

[Guest]

I thought it must be something like that. One last question, please-is there
anyway I can get that cell to round to .38 instead of multiplying the
extended number of .383333333? Thanks again for your help.
Patte

Thanks, Ron. I tried your suggestion and it worked out to the same number
that I had. If I use that cell and multiply it by the hourly wage on the
same spreadsheet it comes out right however when it is linked to another it
was over 3 cents. these are the figures on the first spreadsheet
I29 37:23 K29 =I19*24
37.38
on the second one I have
C4 =[we081404.xls]Sheet1!$K$29 D4 $7.50 E4 =SUM(C4*D4)
37.38
280.38 should be 280.35
I have C4 and E4 formatted as a number cell and D4 as a currency cell
I think there must be something I'm missing between the two spreadsheets.
I've asked others who use excel more that I do and they said this happens to
them but I'm sure there must be a way to make it correct.
Thanks again. Patte

Patte,

Excel is calculating correctly.

The reason you get $280.38 is because you are not multiplying 37.38 * 7.5.

You are actually multiplying 37.383333333333... * 7.5 which gives 280.375

37 hrs 23 minutes is not exactly 37.38. 23/60 = 0.3833333333... + 37 =
37.383333333

So the 'correct' answer is, in fact, $280.38 (rounded to the nearest cent).

=================

I suspect other numbers which have been classified as irregular can be
explained by the same phenomenon.


--ron

 

[Guest]

Many thanks, that worked and...you added to my vocabulary although I had to
look it up (superfluous)!
Patte

I thought it must be something like that. One last question, please-is there
anyway I can get that cell to round to .38 instead of multiplying the
extended number of .383333333? Thanks again for your help.
Patte

Just use the Round function:

C4 =Round([we081404.xls]Sheet1!$K$29,2)

Oh, by the way, the SUM function in E4 =SUM(C4*D4) is superfluous.

Better: E4 =C4*D4




--ron

 

[Ron Rosenfeld]

Many thanks, that worked and...you added to my vocabulary although I had to
look it up (superfluous)!
Patte

You're welcome.

Glad to help.


--ron

 

[Guest]

Ron, I followed with interest your discussion with Patte from Florida,
because I struggle with the same thing in my payroll spreadsheets. I had been
using a column called, "Convert to Currency" in which I hand typed the
rounded number, then based my other formulae on that column.

Could you explain each piece of the syntax in the round formula that you
gave, please? I'm okay until I reach the !$K$29,2) part, then I don't follow.

Thanks! Susan in Payroll

Many thanks, that worked and...you added to my vocabulary although I had to
look it up (superfluous)!
Patte

I thought it must be something like that. One last question, please-is there
anyway I can get that cell to round to .38 instead of multiplying the
extended number of .383333333? Thanks again for your help.
Patte

Just use the Round function:

C4 =Round([we081404.xls]Sheet1!$K$29,2)

Oh, by the way, the SUM function in E4 =SUM(C4*D4) is superfluous.

Better: E4 =C4*D4




--ron