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