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