If your spreadsheet contains a list of times, it is useful to group time in Excel into intervals such as 30 minutes, hourly or every 6 hours.
Fortunately, Excel provides a variety of methods to group time depending on what you need. In this blog post, we look at 4 ways to group time in Excel using PivotTables and formulas.
Group Time by the Hour using PivotTables
PivotTables make it very easy to group numeric data such as dates and times including grouping time by the hour.
If you have a list of times, simply drag the time column into the Rows area of the PivotTable.
In Excel 2016, the PivotTable automatically groups it by the hour, minute and second. Previous versions do not, but have the same options.
Group a time field by right mouse clicking on a cell containing a time and select Group. Then choose the group options you want.
Group Times to Specific Intervals
PivotTables are great, but they cannot help if you want to group time into specific intervals such as every 30 minutes, or every 4 hours.
In this video, the FLOOR function is used to group time into 6 hour intervals, each quarter of the day.
We then create a PivotTable and use these intervals as a label in the report.
Round Time in Excel to the Nearest 15 Minutes
The FLOOR function will always round down to a multiple that you specify. If you need to round to the nearest multiple, it is not good enough.
Say for example that you want to round to the nearest 15 minutes. So a time of 00:18:30 should be rounded down to 00:15:00, but a time of 00:22:40 should round up to 00:30:00.
For this we can use the MROUND function of Excel. This function will round a value to the nearest multiple that you specify, which in this example is 15 minutes.
Group Times into Irregular Intervals using VLOOKUP
In the final example we look at using the VLOOKUP function to group time into irregular intervals.
In this video to day is split into morning, day, evening and night. However these parts of the day are not specific intervals such as every 6 hours. The morning is 5 hours between 06:00 and 11:00, then the daytime is 6 hours between 11:00 and 17:00 etc.
These intervals are set by creating a lookup table. A VLOOKUP is then set up to do an approximate match (range lookup) for each time within this table.
Leave a Reply