For business users of Microsoft Excel Free guides and templates

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, 2005-2014
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 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 RAND()'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.


How to Use Excel's Better-Known Rounding Functions: INT, ROUNDUP, ROUNDDOWN, & ROUND

Overview of Excel's Rounding Functions

Overview of Excel's Date Functions

Free Excel Dashboards

Charley's SwipeFile charts