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.

1515

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

The table shows how the formula rounds a time upward to the nearest half hour

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.