How to Group Time by Hour or Minute

This post will guide you how to group time by hours intervals or group time by minute intervals in excel. How to group time by 3 hours using excel formula? How to group time by 15 minutes with formula in excel.

Group Time by Hour or Minute

If you have a list of times in your worksheet and wonder if you want to group them into intervals such as 3 hours, one hour or 15 minutes.  How to quickly group time in excel? You can use a rounding function named as FLOOR in excel.  This function can be used to round the times based on the significance that you specified.

You can use the FLOOR function to create a generic formula as follows:

=FLOOR(number,significance)

Number – the number that you want to round down

Significance – the multiple of significance that you want to round a number to.

If the time is 16:00 PM and the significance value as “3:00” , then the FLOOR function should be round the time value down to the 3 hours intervals, and it will return as: 15:00 PM.

Suppose you need to group times in range A2:A6 by 3 hours, then you can write down the following formula:

=FLOOR(A2,”3:00”)

1# you can enter this formula into cell B2, then press Enter key.

group time by hour1

2# select cell B1, right click on it, select Format Cells…, switch to Number tab, choose Time under Category list. Then click “OK” button.

group time by hour2

3# let’s see the result.

group time by hour3

4# drag the AutoFill handle down to apply this formula to other cells

group time by hour4

If you want to group time by 15 minutes in your list of times in range A2:A6, then you use the following formula:

=FLOOR(A2, “0.05”)

Or

=FLOOR(A2,TIME(0,15,0))

You can enter this formula into Cell B1, then press Enter.

group time by hour5

Group time with pivot table

You can also use pivot table to group times by the hour, but it only be able to group the times by 1 hour increments. It can’t group into other specific time intervals, such as: 15 minutes, or 3 hours.
1# Go to “INSERT” Tab, then click Tables -> PivotTable. Then Create PivotTable window will appear.

group hour with pivottable1

2# select a table or range A1:A6, then click “OK” button.

group hour with pivottable6

3# Choose Times field to the Rows area.

group hour with pivottable7

4# right click on any cell in the Rows area and choose Group…

group hour with pivottable8

5# choose Hours from Grouping window

group hour with pivottable9

6# you should see that the times have been grouped by 1 hour.

group hour with pivottable10


 Related Functions

  • Excel FLOOR function
    The Excel FLOOR function returns a number rounded down to the nearest multiple of significance. So it will return a rounded number.The syntax of the FLOOR function is as below:= FLOOR (number, significance)…
  • Excel TIME function
    The Excel TIME function returns a date in serial number format for a particular time (the hour, minute and second).The syntax of the TIME function is as below:= TIME(hour, minute, second)…
Related Posts
How to Compare Time Difference with Certain Time in Excel
Compare Time Difference with Certain Time 3

Sometimes we need to count time difference when participating a contest or an exam, and if the time difference (between start time and end time) is greater than certain time, participants are failed in the contest. So, in this situation ...

How to Convert Text to Time in Excel
convert text to time7

This post will guide you how to convert time string to time in Excel. Assuming that you have a list of text string in your worksheet, and you wish to convert these time string to standard time format, how to ...

How to Extract Time From A Date And Time in Excel
extract time from date and time11

This post will guide you how to extract time portion from a date and time in Excel. How do I remove date on Date/Time Stamp with a formula in Excel. How to remove date from a date and time value ...

Extract Time or Hour Only from a Date and Time in Excel
extract time and hour7

This post will guide you how to extract time from a data and time value with a formula in Excel. How do I extract time from a date-time number in Excel 2013/2016. How to extract hour only from the date ...

Adding Hours, Minutes, or Seconds to a Date and Time in Excel
adding dates4

This post will guide you how to add hours, minutes, or seconds to a given date and time in a cell in Excel. How do I add hours, minutes or seconds to a time with a formula in Excel. How ...

Excel XIRR Function
excel xirr examples1

This post will guide you how to use Excel XIRR function with syntax and examples in Microsoft excel. Description The Excel XIRR function returns the internal rate of return for a series of cash flows that is not necessarily periodic. ...

Excel VDB Function
excel vdb examples1

This post will guide you how to use Excel VDB function with syntax and examples in Microsoft excel. Description The Excel VDB function calculates the depreciation of an asset for a specified period based on the double declining balance method. ...

Excel SYD Function
excel syd examples1

This post will guide you how to use Excel SYD function with syntax and examples in Microsoft excel. Description The Excel SYD function calculates the sum-of-years’digits depreciation of an asset for a specified period. And it will return a numeric ...

Excel SLN Function
excel sln examples1

This post will guide you how to use Excel SLN function with syntax and examples in Microsoft excel. Description The Excel SLN function calculates the depreciation of an asset for one period based on the straight line depreciation. And it ...

Excel PV Function
excel pv examples1

This post will guide you how to use Excel PV function with syntax and examples in Microsoft excel. Description The Excel PV function returns the present value of a loan or investment based on constant payments and a constant interest ...

Sidebar