Explanation
If you need to group times into buckets (i.e. group by 6 hours, group by 3 hours, etc.) you can do so with a rounding function called FLOOR.
In the example shown, we have a number of transactions, each with a timestamp. Let’s say you want to group these transactions into buckets of 3 hours like this:
12:00 AM-3:00 AM 3:00 AM-6:00 AM 6:00 AM-9:00 AM 9:00 AM-12:00 PM
For example, a time of 2:30 AM, needs to go into the 12:00 AM - 3:00 AM bucket. A time of 8:45 AM needs to go into the 6:00 AM-9:00 AM bucket, and so on.
If you think about it, one way to do this is to round each time until it fits into the right bucket. However, unlike normal rounding, where we might round to the nearest multiple, in this case, we want to round down to the nearest multiple , starting at midnight.
Because Excel times are just decimal numbers , you can easily do this with the FLOOR function , which rounds down to a multiple that you supply (FLOOR calls the argument that represents multiple “significance”). Even better, FLOOR understands how to round time provided in a format like “h:mm” (for example, “3:00”, “12:00”, etc.).
In the example shown, the formula in E5 is:
=FLOOR(D5,"3:00")
FLOOR knows how to read time, so it interprets 3:00 as its decimal equivalent, 0.125. It then simple rounds down each time to the nearest multiple of 0.125 You can use this same approach to group times into any standard bucket that you like.
If you have times that span one or more days, you can use the MOD function to extract just the time, as explained here .
Pivot tables
Pivot tables will automatically group times into buckets of 1 hour, but they can’t automatically group into other time buckets. However, using the approach outlined on this page, you can group time as you like, then run the resulting data through a pivot table to summarize.
Explanation
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:
=VLOOKUP(D5,buckets,2,1)
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 .
