Rounding based on a cut-off number

1. Rounding based on a cut-off number

Hello,

I am trying to round times in a way that is inconsistent with the default MROUND command

I need to round times to the half-hour, but based on a cut-off of :16, rather than the Excel default of :15 (i.e., MROUND). Exact half-hour and hour times should remain unchanged.

Examples:
1:15 rounds down to 1:00 and 1:16 rounds up to 1:30.
1:43 rounds down to 1:30 and 1:47 rounds up to 2:00.
1:30 = 1:30 and 2:00 = 2:00

Is there an easy way to do this? Thanks for the assistance!  Register To Reply

2. Re: Rounding based on a cut-off number

 a b c 2 1:15 1:00 b2: =mround(a2 - "0:1", "0:30") 3 1:16 1:30 4 1:30 1:30 5 1:43 1:30 6 1:47 2:00  Register To Reply

3. Re: Rounding based on a cut-off number

What is the precision of these times? Are all of them exact minutes, or are there seconds involved? What should 1:15:30 round to (assuming seconds are involved)? I don't understand the 3/4 hour -- what should 1:44:00 round to? 1:45:00? 1:46:00?

My idea would be to still use MROUND(), but modify the time value a little. Maybe subtract 30 seconds (or other suitable value) from the time then MROUND().

MROUND(time(1,16,0)-time(0,0,30),time(0,30,0)) should yield 1:30:00, but MROUND(time(1,15,0)-time(0,0,30),time(0,30,0)) should return 1:00:00.

Would something like that work, or am I misunderstanding something?  Register To Reply