How to group time by minutes or hours intervals in Excel?
When working with time data in Excel, there are often scenarios where you need to organize or analyze time entries by regular intervals, such as grouping every 3 hours or every 15 minutes together. This can make summaries, analysis, and reporting clearer, especially when dealing with large sets of time-stamped data, work shifts, log entries, or scheduling tasks. For example, in the list of times below, the goal is to group the times into 3-hour blocks for easier analysis, as showcased in the screenshot.
Group time by minutes intervals
Group time by intervals using Kutools for Excel
Group time by hours intervals
To efficiently group a list of time values into fixed hour intervals (for example, every 3 hours), you can apply a straightforward Excel formula using the FLOOR function. This method is especially suitable if your dataset is listed in a single column and you want to clearly identify the starting point of each interval.
Select the cell next to your time column (assuming your first time entry is in cell A2), and enter the following formula:
=FLOOR(A2,"3:00")Here, A2 refers to the time value you want to group, and 3:00 signifies each 3-hour interval. After entering the formula, press Enter to confirm, then use the fill handle to drag the formula down alongside your data to apply it to the rest of the column.
This approach is flexible—simply adjust the interval text (for example, use "6:00" for 6-hour intervals) to match your requirement.
If the resulting grouped times appear as general numbers rather than readable times, select the results, right-click, choose Format Cells > Time, then pick the desired time format. This ensures your grouped values display as times.
This formula solution is direct and easy to use, ideal when you need quick grouping and wish to reference interval groups in further calculations or use them as criteria for filtering and analysis. Be mindful that the formula always 'rounds down' to the nearest interval.
Group time by minutes intervals
To group time into specific minute intervals, such as every 15 minutes, Excel’s FLOOR function can be paired with the TIME function for flexibility. This is frequently used for logs or activity records where rounding or grouping by short time periods is needed.
In a cell adjacent to your time data (again, starting in A2), enter the following formula:
=FLOOR(A2,TIME(0,15,0))In this formula, A2 points to your source cell, and TIME(0,15,0) means each interval is15 minutes. Confirm by pressing Enter, and extend the formula down for each row you need.
If the column displays numbers (e.g.,0.625) instead of times, format the cells: right-click the range, choose Format Cells > Time, and select an appropriate time format.
You can customize the interval by adjusting the formula. For example, TIME(0,30,0) for 30-minute intervals, or TIME(1,0,0) for 1-hour intervals. When applying to a large range, it's crucial to check that all original time values are genuine Excel time values, not text. If you encounter errors or unexpected results, check for any text-format or mixed-format issues in your source column.
This method is most useful for standard reports where group boundaries need to be clear. The technique rounds times "down" to the nearest specified interval, making it easy to sort, filter, or aggregate your data based on the grouped time blocks.
Group time by intervals using Kutools for Excel
Kutools for Excel’s Pivot Table Special Time Grouping feature allows you to group dates in a Pivot Table by week, weekday, half-year, fiscal year, and more — even in ways that native Excel doesn't support easily.
Step 1. Select your dataset, go to Kutools Plus > Pivot Table > PivotTable Special Time Grouping.
Step 2. Choose your grouping type, such as: Week Number, Day of Week, Half Year, Fiscal Year (with custom start month), Half an Hour, Minutes.
Step 4. Select where to output the pivottable.Click OK,
Step 5. In the PivotTable Fields pane, drag the Time field into the Values area. You'll now see that the time entries have been grouped automatically.
In addition, Kutools has also added a new field called Minutes, which groups the time data into 15-minute intervals based on the original time column.
Unlock Excel Magic with Kutools AI
- Smart Execution: Perform cell operations, analyze data, and create charts—all driven by simple commands.
- Custom Formulas: Generate tailored formulas to streamline your workflows.
- VBA Coding: Write and implement VBA code effortlessly.
- Formula Interpretation: Understand complex formulas with ease.
- Text Translation: Break language barriers within your spreadsheets.
Relative Articles
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in