# How to round off an hour value?

I need to round some hour value in excel at the first quarter after):

• 9:01 ==> 9:15
• 9:10 ==> 9:15
• 9:15 ==> 9:15
• 9:16 ==> 9:30

and so on

You can use `CEILING` directly on the whole value

`=CEILING(A1,"0:15")`

Format as a time value

• Well darn, if you ain't right. Had to test it, myself, because I didn't think that would work on a stored time value. Thanks. +1 May 1, 2015 at 17:39
• Thanks - you can do similar with `FLOOR` or `MROUND`, depending on whether you want to round up, down or to the nearest 15 May 1, 2015 at 17:40

You can use the ceiling function.

eg. If 9:01 is stored in cell A1:

``````=TIME(HOUR(A1),CEILING(MINUTE(A1),15),0)
``````

The breakdown of the formula explains:

`Minute(A1)` - convert your time 9:01 into 1 minute.

`Hour(A1)` - convert your time 9:01 into hour ie. 9.

`Ceiling(Minute(A1),15)` - With that 1 minute, excel will roundup to the first quarter according to the multiple of significance your set, ie. 15.

Finally, we have all the ingredients ready, and will combine all into time format by indicating the hour, minute and second using:

`TIME(HOUR(A1),CEILING(MINUTE(A1),15),0)`- hour=9 ; minute=15 (After round up) ; seconds not required so 0.

Hope that helps.

Additional note, by any chance if your case as mentioned below in others' comments (need to consider seconds in your time as well), you can add the rounded up seconds into your minute calculation and follow by rounding up to 15 minute interval.

`=TIME(HOUR(A1),CEILING(MINUTE(A1)+SECOND(A1),15),0)`

p.s. I would suggest keeping your formula as simple as you could whenever you model and omit any unnecessary consideration. If you deal with humongous amount of data or large number of formulas in your excel sheet, things might slow down.

• This fails for times in the first 59 seconds of a quarter hour; 9:15:01 through 9:15:59 get rounded down to 9:15. Apr 30, 2015 at 11:41
• but for this case there is no seconds included Apr 30, 2015 at 15:22
• The example data in the question don't include any seconds.  Neither do they include 9:00, 9:02, 9:03, 9:04, ..., 10:00, 10:01, 10:02, ....  Making broad assumptions about all the input data based on what values the OP didn't include in his list of four examples is treacherous. Apr 30, 2015 at 19:32
• well if you say so, it isn't much trouble to just include second consideration in the formula by addition: '=TIME(HOUR(A1),CEILING(MINUTE(A1),15),0)' to '=TIME(HOUR(A1),CEILING(MINUTE(A1)+SECOND(A1),15),0)' I will add in my answer above. May 1, 2015 at 1:08

Use

`=-INT(-96*original_unrounded_value) / 96`

The unit of dates and times in Excel is the day; (Tuesday at 9:00) + 1 = (Wednesday at 9:00).  Multiply by 96 (=24×4) to get it into units of a quarter hour: (Tuesday at 9:00) + 1 = (Tuesday at 9:15).  Now (Tuesday at 9:00) and (Tuesday at 9:15) are integers — consecutive integers, to be precise — and all the times between them are non-integers (i.e., fractions).  Let's pretend, for simplicity, that (Tuesday at 9:00) = 36 (9×4), (Tuesday at 9:15) = 37 (9×4+1), and (Tuesday at 9:05) ≅ 36.333.

When you want to round things, one of the obvious choices is the `INT()` function:

• `INT(1) = 1`
• `INT(1.5) = 1`
• `INT(1.9) = 1`
• `INT(2) = 2`

As you can see, `INT()` chops down to the next lower integer.  You want to chop upward (9:01 → 9:15), so we take the negative of the number to reverse the direction.  For example, 9:05 → 36.333 → -36.333; and `INT(-36.333)` = -37 (because `INT()` chops down).  Then change the sign again (→ 37) and divide by 96 to get 37/96 = (9¼)/24 → 9:15.

To get the computed values to look like times instead of numbers, format the cells as "Time".