Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: rounding

  1. #1
    New Member
    Join Date
    Mar 2002
    Location
    Central NY(not the park)
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I am trying to write a function to round a number from a specific cell up or down to the nearest quarter of an hour. Time is written in military 24hr format.

  2. #2
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Scott

    I haven't given this a thorough test, but

    =MAX(FLOOR(D7,0.0104166666666667),CEILING(D7,0.0104166666666667))

    Should do the trick

  3. #3
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-28 18:40, ssjerauld wrote:
    I am trying to write a function to round a number from a specific cell up or down to the nearest quarter of an hour. Time is written in military 24hr format.
    Hi ssjerauld:
    If time is in cell B4, use the following:

    to round up to quarter hour use:
    =CEILING(B4,0.25/24)

    to round down to quarter hour use:
    =FLOOR(B4,0.25/24)

    Please post back if it works for you ... otherwise explain a little further and let us take it from there!


    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  4. #4
    New Member
    Join Date
    Mar 2002
    Location
    Central NY(not the park)
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    A little more info. I may have misspoken, time is in 10ths & 100ths. Actual Function should round 10.12 to 10.00, 8.93 to 9.00, 9.21 to 9.25, 8.42 to 8.50 and so on on on.
    Am using MS OFFICE EXCEL 97
    Sorry & Thanks

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,425
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default



    the following will round time to the nearest 15 minutes,
    if you need it as a number just *24 it.

    =MROUND(D18,"00:15:00")
    =MROUND(D18,"00:15:00")*24

    If this function is not available, run the Setup program to install the Analysis ToolPak. After you install the Analysis ToolPak, you must enable it by using the Add-Ins command on the Tools menu.

  6. #6
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi ssjerauld


    Are you now saying the time is not in 24 hour time?

  7. #7
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    If so try

    =MROUND(C6,0.25)

  8. #8
    New Member
    Join Date
    Mar 2002
    Location
    Central NY(not the park)
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Dave,
    Yes the time is still 24hr clock. 01.00=1:00AM, 24.50=12:30PM. However the cell that I am targeting is for the Dailt Totals which are normaly in the 8 to 14 hour range.

  9. #9
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,425
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default


    To round in .25 increments on decimal numbers not hours

    =ROUND(B65*4,0)/4

    Revise reference as necessary.

    or back to hours converted to decimal

    =MROUND(C2,"00:15:00")*24

  10. #10
    New Member
    Join Date
    Mar 2002
    Location
    Central NY(not the park)
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Dave,
    We have a winner! =ROUND(B65*4,0)/4 did the trick.
    THANKS EVERYONE !

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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