# Thread: Rounding up times (Excel 2003)

1. ## 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. ## 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. ## 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. ## 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. ## 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?

#### Posting Permissions

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