How to Group Time by Minutes or Hours Interval in Excel?

This article is designed for user to group the time data by using the two examples for minutes, and hours. To understand the process in depth it is better to explain both the processes separately by using different example. Interval can be defined as the difference in between two provided time periods. For example, if first time is 2 o clock and another time is 4 o clock, then the interval between them is of 2 hours( if evaluated in terms of hours).

Examples to Group Time by Hours in Excel

Example 1

Step 1

First open the spreadsheet and create a sample data. For this example, let’s assume the below given tabular data:

Step 2

Before, moving to the answer it is necessary to check the column data type for output column, that is D column for this case. To do so, select the whole D column, by clicking on the D column header.

Step 3

Go to the “Home” tab, under the “Number” section, click on the first drop down list, and select “Time”. This will change the column data type to time.

Step 4

After that, to generate the required solution, consider the valid interval in hour. For this example 2-hour interval is considered. After that go to the D3 cell, and type the below given formula:

=FLOOR(C3,"2:00")

Understand the structure of formula:

Here, C3 is the column name that contains the data.

And, “2:00” is the time duration in hours.

Floor() method is used to round the number to the nearest possible integer value.

The Math.floor() method rounds a number DOWN to the nearest integer.

Step 5

The image of pasting the above provided formula in the worksheet:

Step 6

Below given data will be displayed in the D3 cell.

Step 7

Click on the bottom of “+” sign available at the bottom of the cell, and drag it to the 6 th row, to obtain data value for remaining cells.

Step 8

After this D column will contain below given data:

Example 2

Step 1

Now, let’s group the data by using the minute intervals in excel

Consider the below given sample data:

Step 2

For this case let’s assume a 20-minute interval. After that go to the D3 cell, and type the below given formula:

=FLOOR(C3,TIME(0,20,0))

Explanation for formula:

Here, C3 is the column name that contains the data.

Here, first “0” indicates the value of hour, “20” refer the time duration in minutes, and “0” refer to the “seconds” value.

Step 3

Press “enter” key, and click on the “+” cell to the 6 row, as done in above steps:

Conclusion

In this article, two simple examples, are used to explain “the process of evaluating time interval in hours and minutes” within excel. After performing all the above provided steps user will be able to understand the proper steps required to evaluate the time interval from the provided cell and the new calculated interval will be printed on the next cell column.