Excel: How to Group Times into Unequal Buckets


You can use the following basic syntax in Excel to group times into unequal buckets:

=IF(B2<=TIMEVALUE("10:00:00 AM"),"12AM-10AM",IF(B2<=TIMEVALUE("1:00:00 PM"),"10AM-1PM","1PM-12AM"))

This particular formula looks at the time in cell B2 and returns the following values:

  • 12AM-10AM if the time value in cell B2 is less than or equal to 10AM.
  • Else, 10AM-1PM if the time value in cell B2 is less than or equal to 1PM.
  • Else, 1PM-12PM

Note that this particular formula creates three distinct time buckets, but you can use as many nested IF functions as you’d like to create even more time buckets.

The following example shows how to use this formula in practice.

Example: How to Group Times into Unequal Buckets in Excel

Suppose we have the following dataset in Excel that shows the times when various employees started working their shift on a particular day:

Suppose we would like to classify each start time into one of the following time buckets:

  • 12AM-10AM
  • 10AM-1PM
  • 1PM-12AM

We can type the following formula into cell C2 to do so:

=IF(B2<=TIMEVALUE("10:00:00 AM"),"12AM-10AM",IF(B2<=TIMEVALUE("1:00:00 PM"),"10AM-1PM","1PM-12AM"))

We can then click and drag this formula down to each remaining cell in column C:

Excel group times into unequal buckets

Column C now shows the time bucket in which each employee started their shift.

For example:

  • Employee A001 started working at 12:33 AM so they fell into time bucket 12AM-10AM.
  • Employee A006 started working at 11:34 AM so they fell into time bucket 12AM-10AM.
  • Employee A008 started working at 3:19 PM so they fell into time bucket 1PM-12AM.

And so on.

Additional Resources

The following tutorials explain how to perform other common tasks in Excel:

Excel: How to Use IF Function with Negative Numbers
Excel: How to Use a Formula for “If Not Empty”
Excel: How to Use a RANK IF Formula

Leave a Reply

Your email address will not be published. Required fields are marked *