Quick, clean, and to the point

Group times into unequal buckets

Excel formula: Group times into unequal buckets
Generic formula 

To group times into buckets that are unequal (i.e. 12 AM-7 AM, 7 AM-12 PM, etc.) you can use  the VLOOKUP function. In the example shown, the formula n E5 is:


If you need to group times into buckets that are not the same size (i.e. 12 AM-7 AM, 7 AM-12 PM, etc.) you can use the VLOOKUP function in approximate match mode.

The problem

There are several ways to group times in Excel. If you just need to group times by the hour, a pivot table is very quick and easy. If you need to group times into other equal buckets of multiple hours (i.e. 3 hours, 4 hours, etc.) a nice solution is to use the FLOOR function. However, if you need to group times into unequal buckets, you need to take a more custom approach. VLOOKUP, in its approximate match mode, allows you to group times into custom intervals of any size.

The solution

The solution is to build a lookup table that "maps" each time into the right bucket. In the first column, enter the start time for the bucket. In column two, enter the name of the bucket you want to use. The table must be sorted by the start time, smallest to largest. Finally, configure the VLOOKUP function to look up each time in the bucket table with approximate match.

In the example shown, the formula n E5 is:


D5 is the lookup value, "buckets" is a named range for G5:H8, 2 is the column index, and 1 is a flag that enables approximate match. (You can also use TRUE). See this page for a full explanation.

When VLOOKUP is in approximate match mode, it matches the nearest value that is less than or equal to the lookup value. In this way, you can think of the incoming lookup time as being "rounded down" into the right bucket.

This formula is a great example of how you can use VLOOKUP to group data in completely custom ways. I learned it from Jon Acampora, over at Excel Campus, in his article on three ways to group times.

Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.