Excel Dates
How to Round Time in Excel to Any Increment
With the help of three functions most Excel users seldom use  INT, MOD, and CEILING  you can round Excel time values to any increment you want.
by Charley Kyd, MBA Microsoft Excel MVP, 20052014
The Father of Spreadsheet Dashboard Reports

Several years ago, a reader asked whether it's possible to
use the ROUND function to round times quarter or halfhour
increments.
I gave him a complex formula that did use the ROUND function.
But I just thought of an easier way to do it. You can see it in
use in the following figure.
Column C in the figure contains formulas that reference the adjacent cells
in column B. This lets me format the cells in column C as dates while
seeing the date serial numbers in column B. The formulas are:
C2: =B2
C3: =B3
C4: =B4
Column B contains both values and formulas:
B2: 42500
I vaguely remembered that 42500 is in the middle of 2016. So I
just entered the value.
B3: =B2+RAND()
Time values go from 0 through 1, and RAND()'s possible values do the
same. So this is an easy way to generate random datetimes for
testing.
B4: =INT(B3)+CEILING(MOD($B3,1),1/48)
This is the formula we care about, so let's take it in pieces:
INT(B3) returns only the date. The remainder
of the formula works only with the time.
MOD($B3,1) returns only the decimal fraction
 that is, the time value  from cell B3. Here's how it works:
The function MOD(number,divisor) returns the remainder after
number is divided by divisor. Because we divide by
1, the remainder is the decimal fraction that we want.
1/48 is the date serial number for each of
the 48 halfhour intervals in one full day. If you want to round
to the nearest 15 minutes, you would use 1/96. If you want to
round to the nearest ten minutes, you would use 1/144. And so
on.
CEILING(number,significance) returns
number rounded up, away from zero, to the nearest multiple
of significance. For example, if you wanted to round a
price of $4.42 upwards to the nearest nickel, you would use the
formula =CEILING(4.42,0.05).
CEILING(MOD($B3,1),1/48) returns the time
value rounded up to the nearest half hour.
Finally, in cell B4, we add the date and rounded time, to get
the date serial number shown.
