Rounding

[theclaah]

How would I round time to the quarter hour?Such as if the time is 8:20 AM I would want to roundDown to 8:15 or in some case i might need to roundUp to 8:30.

[Tom Urtis]

To round down to the quarter hour, so 6:26 = 6:15:
=FLOOR(A1,15/(60*24))
Or
=FLOOR(A1,0.0104166666666667)

To round up so that 6:26 = 6:30:
=CEILING(A1,15/(60*24))
Or
=CEILING(A1,0.0104166666666667)

Note, you can substitute
15/(60*24)
with the constant
0.0104166666666667

Substitute A1 for your cell(s) of interest.

[Steve Hartman]

Use =MROUND(A1,0.0104166666666667) where your date is in A1. This will round up OR down to the NEAREST quarter hour in one step.



[ This Message was edited by: Steve Hartman on 2002-05-03 18:45 ]

[Rocky E]

I am not totally clear on your application, but a simple compound IF will return one result that can give a quarter hour integer. For example if A1 contains the time, formated as time. You can get the number of minutes as number in B1 with =MINUTE(A1). The your quarter hour forumal would be
=IF(B1<15,0,IF(B1<30,15,IF(B1<45,30,45)))

If you require help to merge the result just let me know.


_________________
Hope this helps!

Rocky

"Be not the first by whom the New are try'd,
Nor yet the last to lay the Old aside."
Alexander Pope (1688-1744).

[ This Message was edited by: Rocky E on 2002-05-03 19:18 ]

[Yogi Anand]

On 2002-05-03 18:17, theclaah wrote:
How would I round time to the quarter hour?Such as if the time is 8:20 AM I would want to roundDown to 8:15 or in some case i might need to roundUp to 8:30.

You can use the MROUND function to round to the nearest 15th minute (nearest quarter hour) ... use

=MROUND("8:20 AM","0:15")

If you must round up to the next quarter hour then use the following modification:

=MROUND(IF(MOD(A2,"0:7.5")>0,A2+"0:7.5",A2),"0:15")




_________________
Yogi Anand

Edit: Deleted inactive website from hardcoded signature

[ This Message was edited by: Yogi Anand on 2003-01-19 13:24 ]

[Steve Hartman]

On 2002-05-03 20:41, Yogi Anand wrote:

You can use the MROUND function to round to the nearest 15th minute (nearest quarter hour) ... use

=MROUND("8:20 AM","0:15")

If you must round up to the next quarter hour then use the following modification:

=MROUND(IF(MOD(A2,"0:7.5")>0,A2+"0:7.5",A2),"0:15")




_________________
Yogi Anand
ANAND Enterprises
http://www.handtech.com/anand yogia@hotmail.com

[ This Message was edited by: Yogi Anand on 2002-05-03 20:46 ]

Yogi, MROUND rounds up OR down as it is without the IF and MOD.

[Yogi Anand]

On 2002-05-04 15:58, Steve Hartman wrote:

Yogi, MROUND rounds up OR down as it is without the IF and MOD.

Hi Steve:
MROUND rounds up, away from zero, if the remainder of dividing number by multiple is greater than or equal to half the value of multiple.
So, my second formula which should have correctly read as:

=MROUND(IF(MOD(A2,"0:7:30")>0,A2+"0:7:30",A2),"0:15")

is provided to force rounding up to the next (not the nearest quarter hour ... example:

=MROUND("8:22:00",0:15:00) would produce 8:15

but=MROUND(IF(MOD("8:22:00","0:7:30")>0,"8:22:00"+"0:7:30","8:22:00"),"0:15") would produce 8:30

Regards!

Yogi Anand




[ This Message was edited by: Yogi Anand on 2003-01-19 18:38 ]

[Steve Hartman]

Directly from the help file:

MROUND
See Also

Returns a number rounded to the desired multiple.

Try this for yourself and see:

In cell A1 put any positive number
In cell A2 put 5
In cell A3 put =MROUND(A1,A2)

Now change your number in A1 and see what you get in A3.

27 MROUNDED (to coin a term) to the nearest 5 is 25.
28 MROUNDED to the nearest 5 is 30.
27.4 MROUNDED to the nearest 5 is 25.
27.5 MROUNDED to the nearest 5 is 30, which is what the part of the help file you quoted is talking about.

[Steve Hartman]

theclaah,

On 2002-05-03 18:17, theclaah wrote:
How would I round time to the quarter hour?Such as if the time is 8:20 AM I would want to roundDown to 8:15 or in some case i might need to roundUp to 8:30.

Are you saying sometimes you would want to round 8:20 to 8:15 and sometimes you would want to round 8:20 to 8:30? If so how do you determine when to do which?

[ This Message was edited by: Steve Hartman on 2002-05-06 10:55 ]

[gareth]

The claah writes :
How would I round time to the quarter hour?Such as if the time is 8:20 AM I would want to roundDown to 8:15 or in some case i might need to roundUp to 8:30.
======================

CLAAH

Are you busy with a timesheet for wages or salary,and you want to work out the time worked per employee ? Email me please because I have a formula just for that sort of thing.Dont worry I will share it with the board when I am done. garethl@ij.co.za