2

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

3 Answers 3

2

You can use CEILING directly on the whole value

=CEILING(A1,"0:15")

Format as a time value

2
  • 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
    – fixer1234
    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
2

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.

4
  • 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
    – unacorn
    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.
    – unacorn
    May 1, 2015 at 1:08
1

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".

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .