+ Reply to Thread
Results 1 to 3 of 3

Rounding based on a cut-off number

  1. #1
    Registered User
    Join Date
    01-11-2019
    Location
    USA
    MS-Off Ver
    7
    Posts
    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. #2
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,138

    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
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    10,703

    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?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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