software

need to convert payroll hours to 1/4 of an hour.

e.g 123.56 convert to 123 hrs and 45 minutes
123.78 convert to 124 hours
decimals of 0-25 = 15 minutes
26 - 50 = 30 minutes
51 - 75 = 45 minutes
76 - 99 = 60 minutes

using office 2000

Use

=CEILING(A1,0.25)

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

quot;Flowerquot; gt; wrote in message
...
gt; need to convert payroll hours to 1/4 of an hour.
gt;
gt; e.g 123.56 convert to 123 hrs and 45 minutes
gt; 123.78 convert to 124 hours
gt; decimals of 0-25 = 15 minutes
gt; 26 - 50 = 30 minutes
gt; 51 - 75 = 45 minutes
gt; 76 - 99 = 60 minutes
gt;
gt; using office 2000
This was pretty good to know for future. thanks
I am still unsure how to do my formula for this. Let me explain it again.
the way this company uses the payroll round of minutes is as follows
8.13 to 8.37 = between .13 and .37 is considered 15 minutes
between .38 to .62 (e.g. 8.52) is considered 30 minutes (8 1/2 hours)
..63 to .87 is considered 45 minutes and .88 to .12 is considered 1 hour

the hours are there but the numbers after the decimal points are rounded to
the equilant 15, 30, 45 or 1 hour.

so how do you set the formula for the change only at the decimal levels to
either of the four rounded minutes.

If you can help answer this, I would really appreciate.

Thanksquot;Bob Phillipsquot; wrote:

gt; Use
gt;
gt; =CEILING(A1,0.25)
gt;
gt; --
gt;
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from the email address if mailing direct)
gt;
gt; quot;Flowerquot; gt; wrote in message
gt; ...
gt; gt; need to convert payroll hours to 1/4 of an hour.
gt; gt;
gt; gt; e.g 123.56 convert to 123 hrs and 45 minutes
gt; gt; 123.78 convert to 124 hours
gt; gt; decimals of 0-25 = 15 minutes
gt; gt; 26 - 50 = 30 minutes
gt; gt; 51 - 75 = 45 minutes
gt; gt; 76 - 99 = 60 minutes
gt; gt;
gt; gt; using office 2000
gt;
gt;
gt;

How about, assuming that all the times are in A2:A20, then

=SUMPRODUCT(ROUND(A2:A20*4,0)/4)

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

quot;Flowerquot; gt; wrote in message
news
gt; This was pretty good to know for future. thanks
gt; I am still unsure how to do my formula for this. Let me explain it
again.
gt; the way this company uses the payroll round of minutes is as follows
gt; 8.13 to 8.37 = between .13 and .37 is considered 15 minutes
gt; between .38 to .62 (e.g. 8.52) is considered 30 minutes (8 1/2 hours)
gt; .63 to .87 is considered 45 minutes and .88 to .12 is considered 1 hour
gt;
gt; the hours are there but the numbers after the decimal points are rounded
to
gt; the equilant 15, 30, 45 or 1 hour.
gt;
gt; so how do you set the formula for the change only at the decimal levels to
gt; either of the four rounded minutes.
gt;
gt; If you can help answer this, I would really appreciate.
gt;
gt; Thanks
gt;
gt;
gt; quot;Bob Phillipsquot; wrote:
gt;
gt; gt; Use
gt; gt;
gt; gt; =CEILING(A1,0.25)
gt; gt;
gt; gt; --
gt; gt;
gt; gt; HTH
gt; gt;
gt; gt; Bob Phillips
gt; gt;
gt; gt; (remove nothere from the email address if mailing direct)
gt; gt;
gt; gt; quot;Flowerquot; gt; wrote in message
gt; gt; ...
gt; gt; gt; need to convert payroll hours to 1/4 of an hour.
gt; gt; gt;
gt; gt; gt; e.g 123.56 convert to 123 hrs and 45 minutes
gt; gt; gt; 123.78 convert to 124 hours
gt; gt; gt; decimals of 0-25 = 15 minutes
gt; gt; gt; 26 - 50 = 30 minutes
gt; gt; gt; 51 - 75 = 45 minutes
gt; gt; gt; 76 - 99 = 60 minutes
gt; gt; gt;
gt; gt; gt; using office 2000
gt; gt;
gt; gt;
gt; gt;
hi,

It still did not work.

ok, this how the spreadsheet is being kept from the time card being punched

Date In Out hrs1 In out hrs 2 TTL Hrs
Worked rounds to
2/1/06 8.57 13.57 5.0 14.27 17.03 2.76 7.76
7.45
2/2/06 8.25 13.12 4.87 13.63 17.00 3.37 8.24
8.15
2/3/06 8.00 12.32 4.32 13.10 17.50 4.40 8.72
8.45

Total hours for 3 days 24.72

Rounding is bet. .13 -.37 = .15 minutes
.38 - .62 = .30 minutes
.63 - .87 = .45 minutes
.88 - .12 = 1.00 hour

Since hours change each week the formula should automatically calculate
based on the rounding which could sometimes be 1st category or 2nd category.
maybe and if statement should work, i am not surequot;Bob Phillipsquot; wrote:

gt; How about, assuming that all the times are in A2:A20, then
gt;
gt; =SUMPRODUCT(ROUND(A2:A20*4,0)/4)
gt;
gt; --
gt;
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from the email address if mailing direct)
gt;
gt; quot;Flowerquot; gt; wrote in message
gt; news
gt; gt; This was pretty good to know for future. thanks
gt; gt; I am still unsure how to do my formula for this. Let me explain it
gt; again.
gt; gt; the way this company uses the payroll round of minutes is as follows
gt; gt; 8.13 to 8.37 = between .13 and .37 is considered 15 minutes
gt; gt; between .38 to .62 (e.g. 8.52) is considered 30 minutes (8 1/2 hours)
gt; gt; .63 to .87 is considered 45 minutes and .88 to .12 is considered 1 hour
gt; gt;
gt; gt; the hours are there but the numbers after the decimal points are rounded
gt; to
gt; gt; the equilant 15, 30, 45 or 1 hour.
gt; gt;
gt; gt; so how do you set the formula for the change only at the decimal levels to
gt; gt; either of the four rounded minutes.
gt; gt;
gt; gt; If you can help answer this, I would really appreciate.
gt; gt;
gt; gt; Thanks
gt; gt;
gt; gt;
gt; gt; quot;Bob Phillipsquot; wrote:
gt; gt;
gt; gt; gt; Use
gt; gt; gt;
gt; gt; gt; =CEILING(A1,0.25)
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt;
gt; gt; gt; HTH
gt; gt; gt;
gt; gt; gt; Bob Phillips
gt; gt; gt;
gt; gt; gt; (remove nothere from the email address if mailing direct)
gt; gt; gt;
gt; gt; gt; quot;Flowerquot; gt; wrote in message
gt; gt; gt; ...
gt; gt; gt; gt; need to convert payroll hours to 1/4 of an hour.
gt; gt; gt; gt;
gt; gt; gt; gt; e.g 123.56 convert to 123 hrs and 45 minutes
gt; gt; gt; gt; 123.78 convert to 124 hours
gt; gt; gt; gt; decimals of 0-25 = 15 minutes
gt; gt; gt; gt; 26 - 50 = 30 minutes
gt; gt; gt; gt; 51 - 75 = 45 minutes
gt; gt; gt; gt; 76 - 99 = 60 minutes
gt; gt; gt; gt;
gt; gt; gt; gt; using office 2000
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt;
gt;
gt;