3 Ways to Group Times in Excel - Excel Campus
52

3 Ways to Group Times in Excel

Bottom line: Learn how to group times in a data set for summarized reports and charts.  We will use Pivot Tables, and the FLOOR, TRUNC, and VLOOKUP functions for the different solutions.

Skill level: Intermediate

3 Ways to Group Times in Excel

In this article we are going to learn a few different ways to summarize data based on time groups (increments of hours or minutes).  This is great if you want to analyze trends throughout the hours of a day to see what time you are busiest, and what time you can take a nap… 🙂  We will also see how to create customized and uneven time blocks.

Download

Download the file to follow along.

3 Ways To Group Times In Excel.xlsx (58.3 KB)

The Data Contains a Column with the Date & Time

In this scenario we have a table of sales data that contains one row for each transaction.  The second column contains the date and time the transaction occurred.

Data Set with Date Time Column

The boss wants to see a report that shows how many transactions took place during each hour of the day.  He also wants a report that has groups for every two hours, and another report that combines the overnight hours into one group.

There might be a lot of columns in your data set, but we are really just going to focus on the column that contains the date-time values for this exercise.

Solution #1 – Group Time with a Pivot Table

The quickest and easiest solution would be to use the Group feature in a Pivot Table.  This solution is also the most limiting because you will only be able to group the times in 1 hour increments.

  1. The first step is to create a pivot table and add the Date field to the Rows area.
  2. Right-click any cell in the Rows area and choose Group… (Note: if the Group option is disabled then your date field contains text or blanks.  All cells in the data column of the data set must contain date values.)Group the Date Field for Time
  3. Choose Hours only from the Grouping menu.
    Group The Date Field for Hours
  4. You should now see a list of one hour increments in the Rows area of the pivot table.  The values in the Values area of the pivot table will be summed up to include all transaction that occurred within each hour.  The image below shows that there were 4 transaction between 12:00 AM and 12:59 AM.
    Pivot Table Date Field Grouped for Hours

Please checkout my video series on pivot tables and dashboards for more details on this technique.  I explain how to group dates in the 3rd video of that series.

This is a quick solution, but we are only able to group the times in one hour increments.  We will have to use a different solution to group the transactions in time blocks of 2 hours or more.

Solution #2 – The FLOOR Function

If the boss wants to see the times grouped in increments other than 1 hour, then we can use the FLOOR function to round the times.

Round Times Down with the FLOOR Function

The FLOOR function has two arguments:

=FLOOR(number, significance)

The number is the value to be rounded.  In this case it is our date value.

The significance is the multiple to round the number down to.  We are also able to specify a time value here.

In this case I specified “2:00” to round the time value down to the nearest 2 hour increment.  The nice part is that FLOOR starts at midnight and rounds down in whatever increment you specify.  So 12:25 PM and 1:25 PM will both always round to 12:00 PM.

You can also specify smaller increments to group the times by every half hour “0:30” or every five minutes “0:05”.

What If You Have More Than One Day?

If your data set contains sales for more than one day, then you will want to use the TRUNC function to split the time and date.  The TRUNC function truncates a number to an integer.

Use the TRUNC Function to Return the Time Only

In Excel dates are stored as whole numbers.  Dates with times are stored as numbers with decimals.  So subtracting the date from the truncated date will return only the decimal number, which is the time.

If you format the date: 9/25/15 5:54 AM as a number you will see: 42272.2460

The 42272 is the date, and the .2460 is the time as a fraction of a day (5 hours 54 minutes).

The number 42,272 represents the number of days that have elapsed since Jan 1, 1900.  This is the start of the calendar in Excel.

The [@Date]-TRUNC([@Date] formula returns the .2460, which is the time.

*You will notice that I am using Excel Tables in the image above, but you can use regular cell references as well.

Summarize It With A Pivot Table

This rounding with the FLOOR function has basically assigned a group name to each row of the source data.

You can now add the column that uses the FLOOR function in the Rows area of the Pivot Table, instead of the Grouped Date field.

Group Times in a Pivot Table with the FLOOR Function

This gives us a summary of the transaction count by two hour blocks.  If your data set spans multiple days then this is really going to show you what times of the day you do the most volume.  You could analyze it further to see the days of the week, weeks in the month, etc.

Solution #3 – The VLOOKUP Function

What if we don’t want the same amount of time for each group?  In the image at the beginning of the article I show a report that has a time block from 12 AM – 6 AM (6 hours), then 2 hour intervals for the rest of the day.

Uneven Time Increment Groups in Excel

The boss might want to see it like this because there isn’t much business overnight, and it takes up a lot of space on the report or chart.

Use VLOOKUP To Return The Closest Match – Grouping Technique

For this solution we can use VLOOKUP to lookup the time in a lookup table.

VLOOKUP Time in Lookup Table for Hour Groups

Lookup Table for Hour Increments

The first column in our lookup table contains the time value to lookup.  The second value contains a group name.  This is just text that I created with a formula using the TEXT function.

Notice that the last argument in my VLOOKUP formula is TRUE, which means we are using it to return a closest match.  Here is an article on returning the closest match with VLOOKUP.  It’s a great technique to learn for things like calculating commissions or tax rates.

If you are completely new to VLOOKUP checkout this article where I explain VLOOKUP at Starbucks.

Summarize the Uneven Time Groups with a Pivot Table

Adding the column with the VLOOKUP formulas to the Rows Area of the pivot table gives us a summary of transaction based on the uneven time groups.

Group Times in a Pivot Table with the VLOOKUP Function

One drawback to this method is that you will have to manually sort the group names in the Rows Area of the pivot table.  These group names are text, not numbers, so Excel will not be able to sort them as times.

Conclusion

There are a lot of ways to group times in Excel.  The quickest and easiest method is probably to use the Group feature in a Pivot Table (solution #1).  If you want to group the times in increments of multiple hours or fractions of an hour, then the FLOOR and VLOOKUP functions can help group the times.

Please leave a comment below with any questions.  Thanks!

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here. My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I'm not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 52 comments
Brian - July 19, 2017

I am looking for a faster way to reconcile two different data sets into one data set. On the first report, I have what our Business refers to as a “Lines Extract” which is a list of phone calls made by each phone sales agent. That report contains the agent’s name & unique numeric ID, as well as the date/timestamp of when the call was received & when the call was ended. The second report is a sales report which shows every sale recorded for that agent in a given day. Like the “Lines Extract”, the sales report contains the agent’s name & unique numeric ID, as well as a date/timestamp for when the sale was recorded.

My process current-state is to go through both reports side-by-side & remove what we refer to as “manual sales” which are sales not directly put through by the agent. Once finished, I am left (hopefully) with a reconciled report which shows (1) when a call came to the agent, (2) when their sale was recorded in the system, (3) when the call was released, (4) & how long the call took in total. This process takes hours upon hours. Is there a faster way to accomplish this?

Reply
Phil - July 6, 2017

I am using Excel for Mac 2011. I am trying to enter the FLOOR function and it does not seem to like the [@date] entry. I can’t see if it’s because it’s the Office for Mac program or I am misunderstanding something.

Reply
Michael - February 2, 2017

hello Jon, please i need more clarification on how to range time of advert within the time belt. For instance, between 6:00:00AM-3:00:00PM, 3:00:00PM-7:00:00PM daily etc.Thanks in anticipation

Reply
Iris - January 23, 2017

Yaay! Thank You so much for this.
This used to take me at least 30 minutes to figure out and I managed to do this under a minute.
Your a saviour! 🙂

Reply
Claire - December 16, 2016

Hi I’m wondering if you could help me please with something that is driving me crazy. So I need to identify within my data where a number of police vehicles were out responding to jobs at any one time – say within hour periods. I then want to know how many attended the same jobs within that same time period and how many were are other incidents (at the same time but from the same station)

Thanks very much

Reply
    Jon Acampora - December 20, 2016

    Hi Claire,
    Great question! A lot will depend on how your data is setup, and what you want the report to look like. You might need to add a column to the source data with a formula that uses a COUNTIFS function to calculate this number. It’s going to be difficult to achieve that calculation in a regular pivot table.

    Thanks!

    Reply
      Claire - December 20, 2016

      Thanks very much that is useful as it confirms what I have done since first mailing you. I used the COUNTIFS however as some incidents are within a second of each other its proving a little difficult to analyse down to such minute detail! I’ll plod on – thanks very much, appreciate it.

      Reply
sridhar - November 21, 2016

Hi Jon,

I need to group the data based on 30 minutes.

Scenario:Number of Tickets created every 30 minutes between 9am to 9pm.

I have tried with the Pivot option. I got for 1 hour split. Without rounding the figure can you please help to get this data?

Reply
    Jon Acampora - December 1, 2016

    Hi Sridhar,
    For 30 minute increments you are going to need to use the FLOOR or CEILING function. The Pivot Table won’t group in 30 minute increments like that.

    Reply
JC - November 8, 2016

This is really fantastic stuff Jon! Thanks for the tips. I have a query on this and appreciate your help.

I want to know how to get the Average instead of Count for the transactions in the hourly slots.

Imagining that in our date column we have few months of data, one row for each transaction bearing Transaction no. and date+time stamp (similar to your columns A & B). Lets say data is for six months.

Grouping the hours in the range of 0-1am, 1-2 am, 9-10 pm etc.. and so on will give the TOTAL transactions done during those hourly slots by using COUNT of Trxns in VALUES FIELD in Pivot table.

For example during Six months or 180 days on some of the days the staff worked during night hours. So out of those 180 days staff worked on 10 days between 9-10 pm. Total transaction processed = 45. As staff worked only on 10 days so its Average processing becomes 45/10 = 4.5 transactions during 9-10 pm.

Now this 45 is easy to get by following your tip and using COUNT on grouped pivot table.
My question is how do I get the above 4.5 AVERAGE figure using the grouped Pivot table??

Here’s how I got 10 worked days above. In my manual approach on regular Pivot table I spread all the dates in Columns and Hours in Rows and came up with very wide pivot table with Count of transactions processed in VALUES field. Later I COUNTed the No. of Entries for each row in pivot table (as the cells were blank in those dates where processing did not happen between 9-10 pm). The rows TOTAL/Count gave me the average.

Is it possible to compute this average by some simplistic way within Pivot?

Reply
    Jon Acampora - November 21, 2016

    Hi JC,

    Great question! I don’t believe it’s possible to calculate this within the pivot. You can add a column outside of the pivot that divides the sum of the amount by the count of transactions to get the result.

    This would be possible to calculate with PowerPivot using DAX measures. I don’t have any specific articles on that solution, but I will add it to the list for the future.

    Reply
Elisha Wan - November 7, 2016

I have one column with dates, and another column with numbers. I want to reduce the rows such that for each similar date, I have an average of the numbers.
1-Jan-50 -9999
1-Jan-50 2
2-Jan-50 -9999
2-Jan-50 5
3-Jan-50 -9999
3-Jan-50 0.2
How do I go about it?

Reply
Olivier - August 23, 2016

I have a similar problem and was hoping you have a good solution for me:
I have multiple timesheet records for different employees with start-time and end-time.
I need to sum the total of hours worked by all employees during any hour of the day.

Sample Data:
Employee 1 | Start: 8:30 | End 11:00
Employee 2 | Start: 9:00 | End 12:00
Employee 3 | Start: 9:30 | End 12:00

Result:
8-9: 0.5 hours
9-10: 2.5 hours
10-11: 3.0 hours
11-12: 2.0 hours

Can this be done with an Excel Pivot table?
Thanks a lot in advance
Olivier

Reply
    Jon Acampora - August 26, 2016

    Hi Oliver,
    Great question! The source data is not really in a good format for a pivot table to make this calculation/grouping. As least not that I can think of. You are probably going to need formulas to solve this one. It’s a great challenge though. I’ll put it on the list for a future post. Thanks!

    Reply
CRF4DCI - August 22, 2016

Hello,

Mr. Jon, any chance you might be able to help me with a small excel/vb issue. I have vb code that time stamps and records a cell value anytime it changes (below). What I need is for this it to happen every 60 seconds regardless if the value changes or not. Thus, if the value is the same for ten minutes, I would have ten records each a min apart recording the same value.

Thanks in advance

Clete

Private Sub Worksheet_Calculate()
Sheets(“Log”).Range(“A65536”).End(xlUp).Offset(1, 0) = Now
Sheets(“Log”).Range(“A65536”).End(xlUp).Offset(0, 1) = Sheets(“Log”).Range(“B2”).Value
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = “$B$2” Then
Sheets(“Log”).Range(“A65536”).End(xlUp).Offset(1, 0) = Now
Sheets(“Log”).Range(“A65536”).End(xlUp).Offset(0, 1) = Target.Value
End If
End Sub

Reply
    Jon Acampora - August 26, 2016

    Hi Clete,
    I’m not sure how to achieve that unless the macro is running or the user is interacting with the file. If the user is interacting with the file then you could use a worksheet event to run a macro. You can use the Wait method to wait until a specified time, then time stamp based on a condition. I hope that helps get you started. Thanks!

    Reply
Mustafa - August 19, 2016

Hi Jon,

Can you help me solve problem with time series in excel?

thank you in advance.

Reply
Callum - August 9, 2016

Hi John,

Great stuff, very useful!

Im having issue with one aspect of the above formula – the grouping technique.

I created the below range

Min time Range
09:00:00 9 AM-12 PM
12:00:00 12 PM-3 PM
15:00:00 3 PM-5 PM
17:00:00 5 PM-9 AM

for the following data (just a small section from the data):

07/08/2016 23:58:17 5 PM-9 AM
07/08/2016 23:58:18 5 PM-9 AM
07/08/2016 23:58:55 5 PM-9 AM
07/08/2016 23:59:02 5 PM-9 AM
07/08/2016 23:59:22 5 PM-9 AM
08/08/2016 00:00:01 #N/A
08/08/2016 00:00:06 #N/A
08/08/2016 00:00:13 #N/A
08/08/2016 00:01:14 #N/A
08/08/2016 00:01:16 #N/A

However as you can see i receive N/A for any time which is after midnight and before 9am. Every other time grouping works fine Any advice what i am doing wrong?

Thanks in advance 🙂

Callum

Reply
    Jon Acampora - August 14, 2016

    Hi Callum,
    Great question. You will need to add a row to the top of the table with 00:00:00 5PM-9AM. This will include any times on or after midnight. I hope that helps. Let me know if you have any questions. Thanks!

    Reply
Prem Singh - August 2, 2016

wow great function. it is such a great function such as a small spices all together make the food tasty and delicious

Reply
michael - June 17, 2016

Dear Jon,

Appreciate you extended help from your shared site, which is most vital from my day today job routine furthermore

i would like to ask on how to keep track on a huge quantity of vin from the start of fleet departing till the last location.

to insure an correct monitoring of all the vin transported from different location.

Reply
Angele - May 26, 2016

You are just Awesome. Thank you very much

Reply
vicky - May 20, 2016

Hello,
i am working on my master’s thesis on Big Data and i would really appreciate your help. 🙂
database consists of many many columns which shows some indices and the first column (A) is the date from Jan 1, 1950 to Dec 30, 2015. The problem though is that the values of each column don’t start the same date, for example column B may start from Feb 1, 1966, column C from March 1989, cloumn D from Jan 1, 1990 etc.
My first task is to organize my database – Particularly, i have to to group my columns such as the first index of all columns starts on March 1950 and that will be GroupA, the following date is on column D which values starts from April 1, 1950, that will be GroupB etc. In other words, i want to group my columns given the first date of each column. Can we do that in excel?

Thank you,
Vicky

with regards to the first date of my first value in each column.

Reply
    Jon Acampora - May 21, 2016

    Hi Vicky,
    I am sure it can be done, but I don’t fully understand your question. Is it possible for you to send me an example file?

    Thanks!

    Reply
Jason - April 7, 2016

How could I make 3 groups of times like 5am-8am, 8am-5pm, and 5pm to 11pm?

Reply
    Jon Acampora - April 7, 2016

    Great question Jason! For that you can use the VLOOKUP method described above. For your lookup table you would specify the starting times for each group, in the first column of the lookup table.

    5:00 AM
    8:00 AM
    5:00 PM

    The VLOOKUP function will look for the time in the table and return the corresponding Name in column 2. Just remember to set the last argument of VLOOKUP to TRUE for approximate match. See the links to the articles in that section above for more details on how to use VLOOKUP when the last argument is true.

    Let me know if you have any questions. Thanks!

    Reply
Sundas - March 15, 2016

I need to group data and calculate averages for every 5 minutes. Can anyone tell me how to do that?

Reply
    Jon Acampora - March 16, 2016

    Hi Sundas,
    Great question! You could use the FLOOR & TRUNC formula and change the Significance value of the FLOOR function to “0:05”. The formula would look like the following.

    =FLOOR([@Date]-TRUNC([@Date]),”0:05″)

    This would basically round the numbers down to the nearest five minute mark. So 11:04 AM would be rounded to 11:00 AM. 11:06 AM would be rounded to 11:05 AM.

    I hope that helps. Please let me know if you have any questions. Thanks!

    Reply
David Briggs - October 20, 2015

An alternative the the TRUNC function is to use the MOD function. The MOD function returns the remainder after a number is divided by a divisor — in other words, MOD returns the decimal portion of the value.

Your formula =FLOOR([@Date]-TRUNC([@Date]),”2:00″) becomes

=FLOOR(MOD([@Date],1),”2:00″)

Reply
Mike L - October 19, 2015

Bookmarked! This is great information that I will need on a future project. Thanks for sharing your extensive knowledge Jon!

Reply
Excel Roundup 20151019 « Contextures Blog - October 18, 2015

[…] you have a table with time-stamped items, Jon Acampora shows 3 ways that you can groups those items into time blocks. He uses a pivot table, the FLOOR function and […]

Reply
Ken Sadeckas - October 15, 2015

Perfect. I never thought of using TEXT to do that. That’s why you are the pro.
Thanks.

Reply
Catherine - October 14, 2015

Jon,
The ideas you generate and solutions you create are quite amazing.

Q: I am part of a running group and my children also run both cross country and track. I would like to compare running times. These can vary from 10K (hours, mins, secs) to 1500m, 800m distance (mins, secs) and 200m, 100m sprints (sec, 100’s sec).
The idea would be to track completed times at certain parts of the race…i.e at the 1km, 400m, 100m, 50m 10m ..depending on event and then project speed at each section of the race. End product would be to chart fluctuations and analyse progress over a two to four week period.
In your example above you have transaction amounts per block of time…Can I use same idea to capture running time taken in each set distance parameter or is there a better way for this type of enquiry?
Also what would be the best way to format the various times?
I would be comparing like with like… race distance times to same distance.
Thank you
Cathy

Reply
    Jon Acampora - October 14, 2015

    Hi Catherine,
    Great to hear from you! That is a really interesting project. I’m thinking you could log the data in a table with three columns (Event Date, Distance/Run Type, Time). You can store the 100’s of a second in Excel with the following format.

    h:mm:ss.00

    This format would display both the longer times and shorter times. Of course you might want to format it differently if you have a chart that is only displaying times for the 100m. The following format would just display the seconds and decimal.

    s.00

    Shoot me an email if you have some sample data and I’ll take a look. It might make another good followup post to this. It’s fun to take a break from financial data… 🙂

    Thanks!

    Reply
      Jon Acampora - October 14, 2015

      You would probably want to add another column to the table for runner name. This long table format would make it easy to format the data in a pivot table. It also makes it easy to add entries to the table.

      Reply
      Mitch - April 19, 2016

      Jon,

      Formatted a similar data set for track times as mm:ss.00;@ and tried to summarize (using sum) in a pivot table by race sorted from smallest to largest on time for each person. The table rounds to seconds and does not display the true time in fractions of a second as entered in the data sheet. Any suggestions?

      Reply
Best Excel Tutorial - October 14, 2015

Wow! This is really clever. Thanks for this post

Reply
Karen - October 14, 2015

This is great Jon … learned a lot just from 1 newsletter/blog post! Thanks.

Reply
Ken Sadeckas - October 14, 2015

Thanks, This is very good information. I have not used FLOOR before and will now use it in the future. I have the following question, however

In Solution #2 – The FLOOR Function ,you say “You could analyze it further to see the days of the week, weeks in the month, etc.” I think that I would use WEEKDAY function in another column to see the days of the week, but =DAY([@Date]) yields the numerical value of the weekday, not the name of the day. I can solve this with a lookup table of the weekday names or by adding nested IF statements to get the weekday names, but do you have an easier method that would yield the names of the days?

Thanks.

Reply
    Jon Acampora - October 14, 2015

    Thanks Ken!
    You can use the TEXT function to solve this. Let’s say the [@Date] cell contains 11/15/2015. The following formulas will return the day names of the week.

    =TEXT([@Date], “ddd”) = Sun
    =TEXT([@Date], “dddd”) = Sunday

    Let me know if that is what you are looking for. Thanks again!

    Reply

Leave a Reply:

Grab My Free Cheat Sheet!

VLOOKUP Essentials Guide​

Vlookup Essentials Guide 317x407
Plus: Free Video Training Series on the Lookup Formulas!
x