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.).
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 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.
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...
Excel handles dates and times using a system in which dates are serial numbers and times are fractional values. For example, June 1, 2000 12:00 PM is represented in Excel as the number 36678.5, where 36678 is the date (June 1, 2000) and .5 is the...
MROUND rounds to nearest values based on a supplied multiple. When you supply "0:15" as the multiple, Excel internal converts 0:15 into 0.0104166666666667, which is the decimal value that represents 15 minutes, and rounds using that value. You can...
The RAND function generates a decimal number between zero and 1. So, you might get output like this from RAND() in three cells: 0.54739314 0.919767722 0.633760119 Dates in Excel are defined as simple numbers, where 1 = 1 day. This means you can...
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.