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?
