Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Rounding up times (Excel 2003)

    I am looking for a formula that will round up times. For example, from 4:00 to 4:35 is .58 hours...I want this to round back to .50 hours. .......but, I want 4:00 - 4:40, or 4:00 - 4:45 to round to .75 hrs....and I want 4:00-4:50 or 4:00 - 4:55 to round to 1 hour. I was using ceiling(floor) formulas but I cannot make 4:00-4:35 round back to .5

    Any suggestions or help ??

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Rounding up times (Excel 2003)

    Say the start time is in A1 and the end time in B1. The following formula will round to the nearest multiple of 15 minutes:

    =ROUND((B1-A1)*96,0)/96

    If you have installed the Analysis ToolPak add-in, you can also use

    =MROUND(B1-A1,1/96)

    Explanation: Excel measures dates and times in units of 1 day. 15 minutes = 1/4 hour = 1/96 day.

  3. #3
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Rounding up times (Excel 2003)

    Thank you Hans...perhaps I should have added more detail to my question....I have attached a small sample...if you look at the sample, you'll see that the elapsed time is shown in hours (.17; .25; .33 etc etc). I have a formula that deals effectively with any THO (that's Time-and-a-half) overtime that is up to 30 minutes. What I am looking for is a formula that will round DOWN the time on the 4th (16:00-16:35 ...so it will show the .58 hr) as .5 hr....and on the 7th, it will round DOWN the time of .83 hr to .75 hrs

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Rounding up times (Excel 2003)

    That's basically what I proposed. See attached version. If it does not do what you want, please explain.

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Rounding up times (Excel 2003)

    If you change the formula Hans has in cell I3 to:

    <code>
    =ROUNDDOWN(F3*4,0)/4
    </code>

    and copy it down, does that do what you want?
    Legare Coleman

Posting Permissions

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