In cell B6, I have a time value in hours and minutes. I have a formula
that rounds it DOWN to fourths and expresses the hours in 00, .25, .50
and .75. For example, if the time in B6 is 8:26, it produces 8.25
hours. 8:43 produces 8.5 hours. In that manner, it always rounds it
DOWN to the nearest quarter. The formula I'm using is:

=INT(G6*24)+INT(MOD(G6*24,1)/0.25)*0.25

Can somebody please tell me how to modify this formula to round it UP
instead of DOWN? So, for example, 8:01 through 8:15 would produce 8.25
hours. 8:16 through 8:30 would produce 8.50 hours. 8:31 through 8:45
would produce 8.75, and 8:76 through 9:00 would produce 9.0 hours.

I've played with this thing until I'm blue in the face and can't make
it work.

This would save a lot of people a lot of time and errors in hand-
calculating their time sheets at work.

Please reply only to the group.

Thanks a heap,
Ron M.

Here's one way:

=CEILING(G6*24*60,15)/60

Format the cell as number with 2 dp.

Hope this helps.

Pete


On Sep 10, 9:12 pm, wrote:
In cell B6, I have a time value in hours and minutes. I have a formula
that rounds it DOWN to fourths and expresses the hours in 00, .25, .50
and .75. For example, if the time in B6 is 8:26, it produces 8.25
hours. 8:43 produces 8.5 hours. In that manner, it always rounds it
DOWN to the nearest quarter. The formula I'm using is:

=INT(G6*24)+INT(MOD(G6*24,1)/0.25)*0.25

Can somebody please tell me how to modify this formula to round it UP
instead of DOWN? So, for example, 8:01 through 8:15 would produce 8.25
hours. 8:16 through 8:30 would produce 8.50 hours. 8:31 through 8:45
would produce 8.75, and 8:76 through 9:00 would produce 9.0 hours.

I've played with this thing until I'm blue in the face and can't make
it work.

This would save a lot of people a lot of time and errors in hand-
calculating their time sheets at work.

Please reply only to the group.

Thanks a heap,
Ron M.

=CEILING(G6*24,0.25)

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



wrote in message
ups.com...
In cell B6, I have a time value in hours and minutes. I have a formula
that rounds it DOWN to fourths and expresses the hours in 00, .25, .50
and .75. For example, if the time in B6 is 8:26, it produces 8.25
hours. 8:43 produces 8.5 hours. In that manner, it always rounds it
DOWN to the nearest quarter. The formula I'm using is:

=INT(G6*24)+INT(MOD(G6*24,1)/0.25)*0.25

Can somebody please tell me how to modify this formula to round it UP
instead of DOWN? So, for example, 8:01 through 8:15 would produce 8.25
hours. 8:16 through 8:30 would produce 8.50 hours. 8:31 through 8:45
would produce 8.75, and 8:76 through 9:00 would produce 9.0 hours.

I've played with this thing until I'm blue in the face and can't make
it work.

This would save a lot of people a lot of time and errors in hand-
calculating their time sheets at work.

Please reply only to the group.

Thanks a heap,
Ron M.

To Round up try:

ROUNDUP(G6*24*4,0)/4

format as General or number

To Round to nearest 1/4 try:

=ROUND(G6*24*4,0)/4

same format

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


wrote in message
ups.com...
In cell B6, I have a time value in hours and minutes. I have a formula
that rounds it DOWN to fourths and expresses the hours in 00, .25, .50
and .75. For example, if the time in B6 is 8:26, it produces 8.25
hours. 8:43 produces 8.5 hours. In that manner, it always rounds it
DOWN to the nearest quarter. The formula I'm using is:

=INT(G6*24)+INT(MOD(G6*24,1)/0.25)*0.25

Can somebody please tell me how to modify this formula to round it UP
instead of DOWN? So, for example, 8:01 through 8:15 would produce 8.25
hours. 8:16 through 8:30 would produce 8.50 hours. 8:31 through 8:45
would produce 8.75, and 8:76 through 9:00 would produce 9.0 hours.

I've played with this thing until I'm blue in the face and can't make
it work.

This would save a lot of people a lot of time and errors in hand-
calculating their time sheets at work.

Please reply only to the group.

Thanks a heap,
Ron M.

You guys are great. THANK YOU, THANK YOU! Interesting how you all
approached it differently.

Don't even pay for a drink when I'm around.

Thanks again,

Ron M.