Several years ago, a reader asked whether it’s possible to use the ROUND function to round times quarter- or half-hour 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 the RAND function’s possible values do the same. So this is an easy way to generate random date-times 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 half-hour 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.
