Skip to main content

How to group time by minutes or hours intervals in Excel?

Here is a list of time, and what I want to do is to group time by 3 hours as below screenshot shown, how can I quickly solve it in Excel?
doc group time by interval 1

Group time by hours intervals

Group time by minutes intervals

Group time by hours intervals

To group time by hours interval, you just need one formula.

Select a cell next to the time, and type this formula =FLOOR(A2,"3:00"), A2 is the time you use, 3:00 is the hours interval, press Enter key and drag fill handle down to apply this formula to cells.
doc group time by interval 2

Group time by minutes intervals

If you want to group time by minutes intervals, also can use formulas.

Select a cell next to the time, and type this formula =FLOOR(A2,TIME(0,15,0)), A2 is the time you use, 15 is the minutes interval, press Enter key and drag fill handle down to apply this formula to cells.
doc group time by interval 3

Tip: if the formula results are displayed as digits, just need to format them as time format.

Relative Articles

Best Office Productivity Tools

Supports Office/Excel 2007-2021 and 365  |  Available in 44 Languages  |  Easy to Uninstall Completely

Popular Features: Find/Highlight/Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup  |   Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns   |   Move Columns   |   Unhide Columns   |   Compare Columns to Select Same & Different Cells ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar   |  Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells   |  Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets:  12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need Is Just A Click Away...

Supercharge Your Excel Skills: Experience Efficiency Like Never Before with Kutools for Excel  (Full-Featured 30-Day Free Trial)

kte tab 201905

60-Day Unconditional Money-Back GuaranteeRead More... Free Download... Purchase... 

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! (Full-Featured 30-Day Free Trial)
60-Day Unconditional Money-Back GuaranteeRead More... Free Download... Purchase... 
 
Comments (5)
No ratings yet. Be the first to rate!
Khunsha Nasr
Khunsha Nasr
This comment was minimized by the moderator on the site
Good Morning,

I want the time interval for 30 seconds. i used floor command, like this FLOOR(A2,TIME(0,0,30)), where A2 is my cell for the time. But, its giving me right result. Can you help me with this
Reply
0
0
Nicolas C Petraglia
Nicolas C Petraglia
This comment was minimized by the moderator on the site
I have the duration of the data (not time stamp) in each cell. I want to be able to be able to group into less than 5 minute, 5-10 minutes, 10-15 minutes, 15+. How would I do that. Note: the time durations are displayed HH:MM:SS

Thanks
Reply
0
0
Sunny
Sunny     Nicolas C Petraglia
This comment was minimized by the moderator on the site
Hi, Nicolas C Petraglia, try formula =FLOOR(A2,TIME(0,5,0)), and drag auto fill handle down to all cells you want to group. See the screenshot shown:
https://www.extendoffice.com/images/stories/comments/sun-comment/doc_formula_to_group_time.png
Reply
Report
0
0
Guest
Guest
This comment was minimized by the moderator on the site
Hi, I need to figure out how to align a column of data with one-hour timestamps to a column of data with 5-minute timestamps.  I don't want to average the 5-minute data at all, nor do I want to round the timestamp using FLOOR, I just want to distribute the one-hour data points down the column beside the 5-minute data so they line up.
i.e. the one-hour data column must have 11 blank rows so that it aligns with the same ":00" timestamp in the 5-minute row.  How can I do this?  
Reply
0
0
Sunny
Sunny     Guest
This comment was minimized by the moderator on the site
Hi, Guest, I do not understand your problem. My understanding is that insert blank 11 rows to make one-hour timestamps align with 5-minutes timestamps. If so, copy and past the 5-minutes timestamps to the other sheet firstly, then select the one-hour timestamps selection and apply Kutools for Excel's Insert Blank Rows feature and set insert 11 rows per row, and then copy and paste the 5-minutes timestamps back. For more detail for Insert Blank Rows, please visit:https://www.extendoffice.com/product/kutools-for-excel/excel-insert-blank-rows-column.html
For other methods on inserting blank row, please visit:https://www.extendoffice.com/documents/excel/797-excel-insert-blank-row.html
View Attachments (1 / 5)
Reply
Report
0
0
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations