Rounding a Date/Time to the nearest 15 minutes in Microsoft Office Excel

So this is an easy trick that’s hard to actually remember the details for if you have a non-mathematical brain like mine.  If you have to do a bit of analysis and turn to Excel for help (which I seriously recommend), then this formula is very useful to round date/times to the nearest quarter hour for aggregation/pivottable purposes:

Given that a date/time of “19/01/2010 15:57” exists in cell B5:

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

Will give you “19/01/2010 16:00:00”.

Of course you can do the same in C#:

using System;

namespace ConsoleApplication1
    class Program
        static void Main(string[] args)
            var unrounded = new DateTime(2010, 05, 07, 15, 23, 0);

            Console.WriteLine(“{0} rounds to {1}”, unrounded, unrounded.ToNearestQuarterHour());


    public static class DateTimeExtensions
        public static DateTime ToNearestQuarterHour(this DateTime input)
            return new DateTime(input.Year, input.Month, input.Day, input.Hour, (int)(Math.Round(input.Minute / 15D) * 15), 0);

And yes, I know that isn’t clever enough to take seconds and milliseconds into account with the rounding, but it did what I needed so let’s say that’s an exercise for the reader, eh?

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s