**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*

- Select C5
- 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*

## Leave a Comment