Formula for counting occurrence of dates

Hi,

I've got column A which is a long list of date/time values. That's my data set - it represents the date/time when a message was received.

There are multiple values with the same date/time (as we receive many messages per second).

Is it possible, from this limited data set, to produce a bar graph or chart that displays how many messages were received on each date?

So, for example, the bar graph would have the unique date on the x axis, and quantity of messages on y axis.

27/7/2012 would equal 40 if the count of the times that the date 27/7/2012 appeared in the list equalled 40.

My date range currently spans from 27/7/2012 to today 6/8/2012.

I'm sure this is a simple thing to do , but I just can't get my head around it. Would really appreciate some help from you helpful and knowledgeable guys n gals on this forum.

I've attached a copy of my data set here to speed this along as easily as possible. Many thanks in advance. (I'm using Excel 2007)

MsgStats-Times.zip

Hi DBy,

yes that's exactly what I wanted, thank you very much. I've never used pivot tables before (if I'm honest, I just don't understand how to configure them).

Can I just ask how you got the "Count of Messages" field into the pivot table? I've tried following your example to try and learn how you created the table, but I can't seem to find how to add a new selectable field to a pivot table?

thanks again.

Hello
Glad it was useful. Here's a basic idea of how to create your Pivot Table and Chart:

Give your list of Dates/Times a Header, as I have done, then click anywhere in the column.

Go to Insert on the Ribbon and select Pivot Table, the Create Pivot Table dialogue box opens. If there's no other data around your column of messages, then Excel should correctly identify the Table/Range to create the Pivot from, if not select the data manually. Click Ok.

In the Pivot Table Field List drag the Messages field into both the Row Labels and Values area boxes. Then click any date in the Pivot Table and on the Options tab select Group Selection, select Days only and click Ok.

Then select Pivot Chart, again from the Options tab and choose your chart.

Note that you'll have to Refresh this data to reflect any changes in your chart and if you're going to be adding data to the bottom of the Messages column it might be a good idea to convert it into a Table and then the source will be dynamic for your Pivot Table. Take a look at the link below:

http://www.contextures.com/xlPivot01.html

Hope you can follow my attempt at instructions. Come back with any questions and myself or others will try and help.

DBY

You are an absolute star! Thank you for this. It's a simple pivot table/chart for sure, but now that I've seen how it works I can start playing with it a bit and perhaps start expanding it.

Who knows, maybe soon I'll be thinking up loads of new and innovative ways to pivot my data!

pip pip

Mark