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.