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

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.

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.

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.)
3. Choose Hours only from the Grouping menu.
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.

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.

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.

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.

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.

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.

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.

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.

• Pam says:

After using the FLOOR function for 30 minute increments, when I attempt to pull the time into a pivot table the only options that I see are 00:00 and 00:30 as opposed to time frames for the entire day listed by 30 minute intervals, such as 07:00, 07:30, 08:00 etc. how can I get all time frames to show? Thanks in advance. I appreciate your help.

• Tanumita says:

Hi Jon,
Following your posts since a few days and I am loving them!

• joe78man says:

Hey Jon,

I pasted this raw data in my source tab, which I reuse:

1/2/2019 12:28:46
1/2/2019 13:16:32
1/2/2019 09:20:54

And when I go to the pivot and I filter I see:
1/2/2019
1/2/2019
1/2/2019

Any way to filter by day so it would bring 1 date, so if I filter by 2-ene it would show me the three that match the day?
The raw data is formatted as short date and if I use the auto filter I can filter as the pivot would not let me. It’s driving me crazy

• Michelle says:

Hi Jon, I have a similar dataset to Olivier who also left a comment and am hoping you have a good solution for me:
I have a list of different employees with start-time and end-time and Dates.
Sample Data:
Employee 1 | Date: 07/12/2018 | Start: 8:30 | End 15:35
Employee 2 | Date: 07/12/2018 | Start: 9:00 | End 12:00
Employee 3 | Date: 07/12/2018 | Start: 9:30 | End 17:00

I am trying to plot on a chart the number of employees working across each day in 30 min increments from 7am to 7pm.

Do I need to use formulas to work out which increments fall between their start and end times each day OR is there a less convoluted way to achieve this?

Michelle

• Haseeb says:

Hi, I want to group pivot table results based on months but the months range from 25th to 25th not from 1-30 or 1-31. Can you please suggest what to do in this case?

• John de Guzman says:

Hi,

I am not sure what I am messing up. I am trying to replicate the “DATE” tab data and the Floor formula, same same. I get a syntax error that about the name should not have a space. How do I fix this?

• jonathan says:

Hi Jon,

mine is kind of different

I need to range by user by hour but starting hour 6:30pm to 7:30pm,

i’ve notice that pivot table, floor,vlookup,countif functions can’t help and i can’t find a solution for this.

i’ll appreciate any help.

• Soda says:

Hi,
May you please advise me basically how to use [@ ]. I try to apply [@Date] function but I do not how to hit the button to get the [@ ].

Actually my question is to calculate time interval. The data format is already hour time => 12:30:30 AM

Thanks so much for your help.

• Bruno says:

Hello Jon. This is awesome but I didn’t succeed to followup the data and summarize by buckets of months for example over 3 years of data. How should I proceed to get a graph with the sum of each month of a 3 years project?

• Ted Reilly says:

brilliant Thank you so much

• Soumya Vullam says:

Hi Jon,

This is an awesome way to optimize the time in finding the time range –

I wanted to use the time range vlookup method to find out number of tickets raise to the system engineers in an hour gap time frame – i.e., 6am-7am, 7am-8am, 8am -9am etc till 12am and then check what tickets are being raised out of their shift timings and within their shift time.

So I have created this range for the look up table –

Min Time Range
6:00 6:00 AM – 7:00 AM
7:00 7:00 AM – 8:00 AM
8:00 8:00 AM – 9:00 AM
9:00 9:00 AM – 10:00 AM
10:00 10:00 AM – 11:00 AM
11:00 11:00 AM – 12:00 PM
12:00 12:00 PM – 1:00 PM
13:00 1:00 PM – 2:00 PM
14:00 2:00 PM – 3:00 PM
15:00 3:00 PM – 4:00 PM
16:00 4:00 PM – 5:00 PM
17:00 5:00 PM – 6:00 PM
18:00 6:00 PM – 7:00 PM
19:00 7:00 PM – 8:00 PM
20:00 8:00 PM – 9:00 PM
21:00 9:00 PM – 10:00 PM
22:00 11:00 PM – 12:00 AM
23:00 10:00 PM – 11:00 PM

It is just the starting time value given as the Min column.

And I used this following function for the vlookup –
=VLOOKUP((L2-TRUNC(L2)),lookup!A1:B19,2,TRUE)

Activity Date Local Range
03/27/2017 11:51 11:00 AM – 12:00 PM
03/29/2017 10:47 10:00 AM – 11:00 AM
03/27/2017 7:14 7:00 AM – 8:00 AM
03/28/2017 14:13 2:00 PM – 3:00 PM
04/01/2017 12:16 12:00 PM – 1:00 PM
03/30/2017 8:59 #N/A
03/29/2017 15:50 3:00 PM – 4:00 PM
03/31/2017 6:03 #N/A
03/27/2017 11:01 #N/A
03/28/2017 6:27 #N/A
03/28/2017 8:45 #N/A
03/28/2017 12:22 #N/A
03/29/2017 12:08 #N/A
03/29/2017 12:10 #N/A

it is somehow not giving all the values right, and shows up #N/A

Could you help me out with this, where am I going wrong?

Thank you.

• Varun Sinha says:

thanks it will surely help in making my analysis more accurate.

• Gaylord says:

Jon, I’ve learned a lot from your answers to questions. I’m trying to count the number of times active events overlap during a given day. I have two columns showing data for an entire month; one showing start date/times and one showing end date/times. Any help would be greatly appreciated

• Peter Guy says:

Hi Jon, Hoping you can help with something simple that just does not want to give me results. I have a range example below. I want to have the formula of Minimum pull the quickest time from the range. The N/A means there was not participation in Time2 event, so it should be ignored.

Time1 Time2 Time3 Time4 Minimum
00:58:14 #N/A 00:57:48 00:57:52 ???

With thanks Peter

• Rachel says:

Hi Jon,

I am currently analysing dietary data. This dietary data consists of a large excel spreadsheet with a separate food item on each line. Each line contains a column stating both the time and date the food item was consumed, as well as who consumed it. Instead of having this data on an ingredient level, I would like it on the meal level for each subject. Essentially, I need to collate each line based on the time and date the food item was consumed as well as the participant ID number. I am currently trying to collate this data so that instead of one line representing one food item, one line will instead represent one meal. See below!

Date Time Participant no.1 Food item Energy(kcal)

17/08/2017 09:15 1 Water, tap, drinking 0
17/08/2017 13:30 1 Tomatoes, standard, raw 24
17/08/2017 09:15 1 Butter, salted 47
17/08/2017 09:15 1
17/08/2017 09:15 1
17/08/2017 09:15 1
17/08/2017 13:30 1

Any help with this would be greatly appreciated,
I have been struggling with this for days.

• fiezy says:

Hi, my business day in the back-office system is set from 5:15 a.m. to 5:15 a.m. the following day. How do I go about pivoting for each business day? Thanks in advance.

Date Time Sold (\$)
3/10/2017 2:16:10 AM 10
3/10/2017 2:16:40 AM 10
3/10/2017 9:28:08 AM 10
3/10/2017 9:51:22 AM 10
3/10/2017 11:53:57 AM 10
3/10/2017 11:54:11 AM 10
3/10/2017 11:54:26 AM 10
3/10/2017 1:13:02 PM 10
3/10/2017 7:25:08 PM 10
3/10/2017 10:39:16 PM 10
3/10/2017 11:01:19 PM 10
4/10/2017 2:26:47 AM 10
4/10/2017 2:27:07 AM 10
4/10/2017 7:21:23 AM 30
4/10/2017 7:36:59 AM 10
4/10/2017 8:15:22 AM 10
4/10/2017 8:15:45 AM 10
4/10/2017 8:25:31 AM 10
4/10/2017 9:18:53 AM 10
4/10/2017 12:00:44 PM 10
4/10/2017 1:33:11 PM 10
4/10/2017 2:46:10 PM 10
4/10/2017 8:04:28 PM 10
4/10/2017 9:39:34 PM 10
4/10/2017 11:13:14 PM 10
4/10/2017 11:15:15 PM 10
4/10/2017 11:16:25 PM 10
4/10/2017 11:16:44 PM 10
5/10/2017 2:25:28 AM 10
5/10/2017 2:26:14 AM 10
5/10/2017 3:28:04 AM 10
5/10/2017 7:33:09 AM 30
5/10/2017 7:55:20 AM 10
5/10/2017 7:55:51 AM 10
5/10/2017 10:39:05 AM 10
5/10/2017 12:53:09 PM 10
5/10/2017 8:13:30 PM 60
5/10/2017 8:57:14 PM 10

• Lou G says:

I need to know this too…

• gina says:

8/7/2017 3:30:00PM

This is my current date and time format and I’ve tried several others, however after I insert teh pivot table and place this under rows, it always states can not group this column, I am not sure why.

• nikki says:

Hi,
I am trying to graph some data using scatter plot. I have date and time (x axis) and flow [l/S] y-axis but some events stretch over two days and so I have time overlapping, ,how do I get excel to recognize that even though the time is the same it is different dates and should therefor not plot the flow the same place.

• Ann L says:

Jon,

I’m trying to adapt this to summarize activity by “day of the month”. I have a column of dates:

7/1/2016 2:08:40 AM
7/1/2016 2:43:56 AM
7/2/2016 2:08:59 AM

And a column of unique transaction codes:
05191C
52196
165212

I’m trying to chart how many transactions we have on the 1st of the month, the 2nd, the 3rd, (all 31 days) to see what day of the month, regardless of WHAT month it is, has the most transactions.

Thanks!

• Brian says:

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?

• Phil says:

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.

• Michael says:

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

• Iris says:

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.

• Claire says:

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

• 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!

• Claire says:

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.

• sridhar says:

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?

• 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.

• JC says:

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?

• 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.

• Elisha Wan says:

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?

• Olivier says:

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?
Olivier

• 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!

• CRF4DCI says:

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.

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)
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

• 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!

• Mustafa says:

Hi Jon,

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

• Hi Mustafa,
Sorry, I’m not taking on any new projects right now. Thanks!

• Callum says:

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?

Callum

• 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!

• Prem Singh says:

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

• michael says:

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.

• Angele says:

You are just Awesome. Thank you very much

• vicky says:

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.

• 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!

• Jason says:

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

• 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!

• Sundas says:

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

• 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!

• David Briggs says:

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.

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

• Mike L says:

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

• Awesome! Thanks Mike! I really appreciate the positive feedback. ðŸ™‚

• […] 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 […]

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

• Catherine says:

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

• 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!

• 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.

• Mitch says:

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?

• Hi Mitch,
Did you change the number formatting for the values area inside the pivot table? If you want to send me your file I would be happy to take a quick look. jon@excelcampus.com

• Best Excel Tutorial says:

Wow! This is really clever. Thanks for this post

• Karen says:

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

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.

• 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!

Generic filters
Exact matches only
Filter by Custom Post Type