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

2725 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. Early in my career, I worked nearly 20 years as the CFO of turnarounds and startups. But I eventually got burned out fighting continual struggles with cash flow. That's when I started to write about Lotus 1-2-3, the spreadsheet software that I'd been using most of the time during the CFO days. When Excel was about to be introduced for the PC, one of my magazine editors set up a meeting for me to see the product, talk with the developers, and write a cover story about Excel. So I used the first version of Excel before it was launched. And I had also used the first version of VisiCalc before it was launched. And then,