+ Reply to Thread
Results 1 to 7 of 7

How do I round time to the nearest quarter of an hour

  1. #1
    Meghan
    Guest

    How do I round time to the nearest quarter of an hour

    I am not too understanding of excel to any great extent. But I need to round
    time to the nearest quarter.

    Example

    8:11 to 8.25
    4:41 to 4.75

  2. #2
    Forum Contributor
    Join Date
    06-01-2006
    Posts
    324
    I am assuming from you example that you are rounding up the time.

    Here are the two solutions:

    =CEILING(A1, TIME(0,15,0))*24

    =TIME(HOUR(A1),CEILING(MINUTE(A1),15),0)*24
    Google is your best friend!

  3. #3
    Sloth
    Guest

    RE: How do I round time to the nearest quarter of an hour

    If you want the answer in decimal hours use this formula and format the cell
    as a number
    =ROUND(A1*24*4,0)/4

    If you want the answer in Excel time use this formula and format the cell as
    time
    =ROUND(A1*24*4,0)/24/4

    These round to the nearest quarter hour so 4:17 will yield 4.25 or 4:15

    "Meghan" wrote:

    > I am not too understanding of excel to any great extent. But I need to round
    > time to the nearest quarter.
    >
    > Example
    >
    > 8:11 to 8.25
    > 4:41 to 4.75


  4. #4
    Meghan
    Guest

    Re: How do I round time to the nearest quarter of an hour

    Thanks alot these should help

    "Bearacade" wrote:

    >
    > I am assuming from you example that you are rounding up the time.
    >
    > Here are the two solutions:
    >
    > =CEILING(A1, TIME(0,15,0))*24
    >
    > =TIME(HOUR(A1),CEILING(MINUTE(A1),15),0)*24
    >
    >
    > --
    > Bearacade
    >
    >
    > ------------------------------------------------------------------------
    > Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016
    > View this thread: http://www.excelforum.com/showthread...hreadid=558939
    >
    >


  5. #5
    Meghan
    Guest

    RE: How do I round time to the nearest quarter of an hour

    Thanks alot these should really help

    "Sloth" wrote:

    > If you want the answer in decimal hours use this formula and format the cell
    > as a number
    > =ROUND(A1*24*4,0)/4
    >
    > If you want the answer in Excel time use this formula and format the cell as
    > time
    > =ROUND(A1*24*4,0)/24/4
    >
    > These round to the nearest quarter hour so 4:17 will yield 4.25 or 4:15
    >
    > "Meghan" wrote:
    >
    > > I am not too understanding of excel to any great extent. But I need to round
    > > time to the nearest quarter.
    > >
    > > Example
    > >
    > > 8:11 to 8.25
    > > 4:41 to 4.75


  6. #6
    SteveW
    Guest

    Re: How do I round time to the nearest quarter of an hour

    On Thu, 06 Jul 2006 16:39:53 +0100, Bearacade =

    <[email protected]> wrote:

    >
    > I am assuming from you example that you are rounding up the time.
    >
    > Here are the two solutions:
    >
    > =3DCEILING(A1, TIME(0,15,0))*24
    >
    > =3DTIME(HOUR(A1),CEILING(MINUTE(A1),15),0)*24
    >
    >


    Not used Ceiling() before, why won't it accept negative numbers.

    It is meant to round up to - no mention of only working with >=3D0 numbe=
    rs

    -- =

    Steve (3)

  7. #7
    Meghan
    Guest

    Re: How do I round time to the nearest quarter of an hour

    Thank you

    "SteveW" wrote:

    > On Thu, 06 Jul 2006 16:39:53 +0100, Bearacade
    > <[email protected]> wrote:
    >
    > >
    > > I am assuming from you example that you are rounding up the time.
    > >
    > > Here are the two solutions:
    > >
    > > =CEILING(A1, TIME(0,15,0))*24
    > >
    > > =TIME(HOUR(A1),CEILING(MINUTE(A1),15),0)*24
    > >
    > >

    >
    > Not used Ceiling() before, why won't it accept negative numbers.
    >
    > It is meant to round up to - no mention of only working with >=0 numbers
    >
    > --
    > Steve (3)
    >


+ 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