Round time to nearest 15min interval in Excel

Question

I've an excel spreadsheet with a column which has the date and time of a particular event. I would like to round this to the nearest 15 minute interval so that I can count the total number of events in this period. What is the best way to do do the rounding?

Answer 1

If you want to round to the Nearest 15:

Assuming your time is in cell A2

We'll put our new time into B2:

B2 =TIME(HOUR(A2), ROUND((MINUTE(A2)/60)*4, 0) * 15, 0)

If you wanted to always round up or down you replace ROUND with ROUNDUP or ROUNDDOWN

Answer 2

If time is in cell A1:

=ROUND(A1*(24*60/15),0)/(24*60/15)

(Rounding to nearest 15 minute increment)

or

=INT(A1*(24*60/15),0)/(24*60/15)

(Rounding down to last 15 minute increment)

Answer 3

Date and time rounded to nearest 15-minute period (you can always round up/round down using something like INT):

=DATE(YEAR(B1),MONTH(B1),DAY(B1))+TIME(HOUR(B1), ROUND(MINUTE(B1)/15,0)*15, 0)

Assuming cell B1 contains the date time to be rounded. This will return the number in typical serial date fashion (e.g. 39846.64444 = 02/02/2009 15:28) and you need to format your result cell as a date/time to see the value (as with all solutions to this problem). Showing date and time together is not a standard Date or Time format, you need a Custom format to do this.

Answer 4

Since you said you also want the date, how about this:

= (ROUND((A1 * 1440) / 15, 0) * 15) / 1440

Assuming that A1 has the date/time value you want. This takes advantage of the fact that date/time columns in Excel are just numbers (integer portion is the date, fractional portion is the time)

Answer 5

Simpler ??

=B2-MOD(B2,15/24/60)

Knowing that for Excel 1 day = 24 hrs = 1,
15/24/60 (= 0.0104166666666667) is the numeric equivalent equivalent of 15 min.