< Go Back

Group times into 3 hour buckets

Formula

=FLOOR(time,“bucket”)

Explanation

Mathematically, Floor is a function where its input real number is rounded down to the nearest integer number. In Excel, FLOOR has 2 arguments: number and significance; a number is where you input your value and significance shows the multiple of which you want to floor the value. More conveniently, FLOOR is programmed to also work with input as time values, making time grouping easier. If you want to group time values into buckets of 3-hour, you can simply floor those values into multiple of 3.

Example

Let’s say you have a set of transactions, each with a time stamp and you want to group them into 3-hour buckets, for example

3:00 AM to 6:00 AM

6:00 AM to 9:00 AM

9:00 AM to 12:00 AM

12:00 AM to 3:00 PM and so on

Figure 1. Grouping times into buckets of 3-hour

  1. Select C5
  2. Insert the following formula

=FLOOR(B5,"3:00")

FLOOR recognizes the input number and the significance as time values, so it converts them into equivalent decimal numbers. In Excel, 3:00 is equivalent to 0.125 so all input times are floored to multiple of 0.125, thus 3-hour buckets are created.

Notes

Pivot tables can also group times into buckets of 1-hour. However, it cannot do it with different time buckets, eg. 2-hour buckets. Therefore, this method enables you to group as you wish, then put the buckets into pivot tables for analysis if needed.

If your times’ span is over a day, you need to use MOD function to extract the time and then use FLOOR to group them.

You can also set the significance as a reference cell. Make sure the number and the significance arguments are of the same type, for example, time; and if they are both numbers, they have to be either both positive or both negative.

Figure 2. Inputting reference cells into FLOOR

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California
This blog is one way to figure it out. A 20 minute chat session with an expert is a better way.Get step-by-step guidance on your question

Leave a Comment

avatar