# 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!

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

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?

#### Thread Information

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1