Function Problems

How to Group Excel Date and Time Data into 10-Minute Intervals

Aamir Naveed Akram Oct 22, 2026 868 views

Question details

The user needs to group continuous date and time data into specific 10-minute intervals for easier analysis and summarization.

How to Group Excel Date and Time Data into 10-Minute Intervals

Product: Excel
Device & OS: not provided
Scenario: Organizing raw timestamp data into uniform time buckets to analyze trends, aggregate data, or prepare for pivot table generation.
Observed behavior: Timestamps are recorded as exact times, requiring a formula to round them to specific intervals. Errors may occur if the data is stored as text strings rather than numeric date-time values.

Before you start

Verify that your data is formatted as valid date and time values by selecting the cells and pressing Ctrl+1; if they are stored as text, the grouping formulas will return errors.

Solution 1Recommended

Use the FLOOR Function to Round to 10-Minute Intervals

The FLOOR function is the most reliable way to round a timestamp down to the nearest multiple of a specified time unit.

By combining the FLOOR function with the TIME function, you can instruct the spreadsheet to round any exact timestamp down to the start of its corresponding 10-minute block.

Select the target cell

Click on an empty cell adjacent to your first timestamp. For example, if your time data is in A2, select cell B2.

Enter the FLOOR formula

Type the formula =FLOOR(A2,TIME(0,10,0)) into the formula bar. The TIME(0,10,0) segment represents 0 hours, 10 minutes, and 0 seconds.

Format the result cell

Press Enter. Right-click cell B2, select 'Format Cells' (or press Ctrl+1), go to the 'Number' tab, and choose 'Time' to display the result in a readable clock format.

Apply to the entire column

Click and hold the small square (fill handle) in the bottom-right corner of cell B2, and drag it down to apply this grouping formula to the rest of your dataset.

Use the FLOOR Function to Round to 10-Minute Intervals

Rounding Up: If you prefer to round the time up to the end of the 10-minute interval, replace FLOOR with CEILING in the formula: =CEILING(A2,TIME(0,10,0)).

Solution 2

Fix Text-Format Timestamps Using Text to Columns

If your formula produces a #VALUE! error, the timestamps are likely formatted as text. You must convert them to real numbers first.

Group Time Data Efficiently in WPS Office

Use WPS Spreadsheet to Manage and Group Time Data

WPS Spreadsheet provides comprehensive support for time calculation formulas, enabling you to group, convert, and format extensive datasets quickly and reliably.

1. Open your dataset: Launch WPS Spreadsheet and open the file containing your continuous timestamps.
2. Input the formula: In a blank helper column next to your data, enter =FLOOR(A2,TIME(0,10,0)).
3. Format as Time: Select the cell, press Ctrl+1 to open the Format Cells dialog, and apply your preferred Time format.
4. Fill down the data: Double-click the fill handle at the bottom right of the cell to automatically group all remaining rows.

100% compatible with Microsoft Excel date, time, and rounding formulas.Fast and lightweight, easily handling large datasets without lagging.Intuitive right-click menu and ribbon for seamless cell formatting.Free alternative offering professional data analysis tools.

microsoft office alternative - wps office

Frequently Asked Questions

Can I group timestamps into 15-minute or 30-minute intervals instead?

Yes, you can adjust the interval by modifying the TIME function. For a 15-minute interval, use =FLOOR(A2,TIME(0,15,0)). For a 30-minute interval, use =FLOOR(A2,TIME(0,30,0)).

Why does the formula =FLOOR(A2,TIME(0,10,0)) return a #VALUE! error?

A #VALUE! error indicates that the spreadsheet is reading your timestamp as a text string rather than a calculable number. You must convert the text to a valid numerical format using the 'Text to Columns' tool under the Data tab before applying the formula.

Can I use Pivot Tables to group dates by 10-minute intervals natively?

Pivot Tables allow you to natively group dates by Hours, Minutes, or Seconds, but they do not support grouping by specific minute intervals like 10 or 15 minutes. The best workaround is to create a helper column using the FLOOR formula to define the intervals, and then build your Pivot Table using that helper column.

What does the formula =A2+10/24/60 do?

This formula literally adds exactly 10 minutes to the timestamp in cell A2. It does not round or group the data into fixed intervals. If you need standard bucket grouping, the FLOOR function is the correct approach.

Excel & Data Problems