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)

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

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.

Thanks


"Bob Phillips" wrote:

Use

=CEILING(A1,0.25)

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

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

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)

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

Thanks


"Bob Phillips" wrote:

Use

=CEILING(A1,0.25)

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

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

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 sure


"Bob Phillips" wrote:

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)

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

Thanks


"Bob Phillips" wrote:

Use

=CEILING(A1,0.25)

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

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