Osman Goni Ridwan
When we work with time data in Excel, we may need to group time intervals. We can do that in many ways. Of them, I will show the quickest and easiest methods to group time intervals in Excel.
Download Practice Workbook
You can download the practice workbook from here:
3 Ways to Group Time Intervals in Excel
Suppose, you have a dataset of the entry time of employees in a company. And you want to analyze the data to find who joins the office at early hours and who joins very late. You want to group the data in intervals so you can easily see who joins late and who joins in the first hours. I’m describing 3 methods to group time intervals in Excel with clear steps and illustrations.
1. Group Time by Hour Intervals
Using a Pivot table to group, sort, and filter data is very useful. It will automatically group the time in hours and day intervals. Here, follow the steps to use the pivot table.
- At first, go to the Insert tab in the top ribbon.
- Then click on the Pivot Table option.
- Select the data from the worksheet by clicking on the arrow box in the Table/Range option.
- Select the Existing Worksheet option and select a cell to define where to start the pivot table.
- Finally, press OK.
- There, you will see a Pivot Table will create in the worksheet and a “PivotTable Fields” window will appear on the right side of the worksheet.
- Now drag the Entry time to the Rows section and Name to the Values section.
- As a result, you will see the pivot table is showing the numbers of people per hour interval.
💬 Notes:
- By using the pivot table directly to the time column it will work for 1 hour. So if you want to take 30 minutes or any other you can’t make it.
- In addition, it makes intervals without rounding any time. For example, It will take any time data in a range of 7:00:00 AM to 7:59:59 AM in the 7 AM range.
- So, you should use this method to group time by hour intervals.
Read More: How to Group Columns Next to Each Other in Excel (2 Easy Ways)
2. Group Time by Minute Intervals
Now, if you want to group time by the minutes or multiple hours then you have to round the time figure first to the nearest time interval. You can round time to the nearest interval figure using the FLOOR, CEILING, and MROUND functions. First, you have to round the time to the nearest interval then you will group them by the intervals using the pivot table feature.
2.1 Using FLOOR Function
Here, I will show you the formula to round the time into a specific interval like 30 minutes, 1 hour, or any.
- For this go to cell E5 and paste this formula to round the time to the nearest 30 minutes interval.
=FLOOR(D5,"0:30")- Then, drag the Fill Handle icon to paste the formula into the other cells or use Shortcuts Ctrl+C and Ctrl+P to copy and paste.
- The FLOOR function rounds the time to the nearest lower interval of the time.
- As a result, it rounds the time 7:43 AM to the nearest lower interval 7:30 AM
- The format to define interval here is “HH:MM: SS” so here “0:30” defines 0 hours 30 minutes interval.
- So here, the rounded time groups the time into 30 minutes intervals. And the rounded time describes a range of 30 minutes which starts from it. So, group 7:30 AM means a group range 7:30 AM to 8:00 AM
- Now make a pivot table using the data in the existing worksheet by similar steps mentioned before.
- Then, drag the Rounded Time option into the Rows box and the Name option into the Values box.
- As a result, the pivot table will create and show the grouped data into 30 minutes intervals.
2.2 Using CEILING Function
Alternatively, you can also use the CEILING function to round the time to an interval.
- For this go to cell E5 and paste this formula to round the time to the nearest 30 minutes interval.
=CEILING(D5,"0:30")- Then, drag the Fill Handle icon to paste the formula into the other cells or use Shortcuts Ctrl+C and Ctrl+P to copy and paste.
- The CEILING function rounds the time to the nearest higher interval of the time.
- As a result, it rounds the time 7:43 AM to the nearest higher interval of 8.00 AM
- Now, similarly, make a pivot table for the data range. Then drag the rounded time into Rows and names into the Values box.
- Then you will see the pivot table will create and show grouped time data in 30 minutes intervals.
- Here, the group 8:00 AM defines the range 7.30 AM to 8:00 AM.
2.3 Using MROUND Function
The MROUND function rounds the time to the nearest interval. It doesn’t round to lower or higher intervals rather it rounds the time to that one which is the closest interval.
- First, paste this formula into cell E5.
=MROUND(D5,"0:30")- Then, pull the Fill Handle icon to paste the formula into the other cells.
- The MROUND function rounds the time to the nearest interval of the time.
- As a result, it rounds the time 7:43 AM to the nearest higher interval of 8.00 AM and round time 7:12 AM to 7:10 AM
- Now, do the same things as mentioned in the Ceiling Function to create the pivot table.
- Now, the time is grouped into 30 minutes intervals and shows the count of names in each group of intervals.
💬 Notes:
MROUND function works differently than the FLOOR and CEILING function in grouping times for intervals as it rounds to the nearest intervals. For example, it rounds 7:59 AM to 8.00 AM also it rounds 8:08 AM to 8:00 AM. So, you can say for the 8:00 AM group the range is 7:45 AM to 8:15 AM.
Read More: How to Group Items in Excel (3 Easy Methods)
3. Group Time into Any Interval
To remove confusion, you can use the VLOOKUP function to group time intervals where the intervals are described in a table.
- For, this you have to make a table, where the intervals are well defined with the starting time of the interval.
- Then, paste this formula into cell E5.
=VLOOKUP(D5,$G$5:$H$8,2,TRUE)- D5 – the lookup value for which the function will search the table.
- $G$5:$H$8 – The range where the lookup value locates. Use Absolute Reference so you can copy the formula to the other cells.
- TRUE – it will search for an approximate match of the look value. So, here it will take the lower nearest interval from the table.
- Now similarly, follow the steps to create the pivot table.
- In the PivotTable Fields window, drag the “Group Range” into the Rows box and the names of the row to the values box.
- As a result, the pivot table will create. And it is showing the total number of people for each group of the time interval.
Read More: How to Create Multiple Groups in Excel (4 Effective Ways)
Things to Remember
- Using a simple pivot table will give group data on hourly time intervals in Excel.
- You can use the FLOOR function to round the time to lower the nearest time interval. So using pivot table you can make the grouped data with time intervals where the group format is like 8:00 AM stands for the time interval 8:00 AM to 8:30 AM.
- And, the CEILING function rounds the time to the higher nearest time interval. So, here the 8:00 AM group will define the time interval from 7:30 AM to 8:00 AM.
- The MROUND function rounds the time to the nearest time interval. So, it makes groups like 8:00 AM means the group 7:45 AM to 8:15 AM.
- And finally, the VLOOKUP function works with a ready group of time intervals.
Conclusion
In this article, you have found how to group time intervals in Excel by using pivot table, MROUND function, FLOOR function, CEILING function, and VLOOKUP function. I hope you found this article helpful. You can visit our website ExcelDemy to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.
