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());

            Console.ReadLine();
        }
    }

    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?