How To Count The Number Of Times A Date Appears In A List In Excel

Here is a quick tutorial on using PivotTables to count the number of times a date appears in a long list of data, plus how to fix a small quirk in Excel.

You can download the file here and follow along. If you get a preview, look for the download arrow in the upper right hand corner.

Here I have a list of 2,000 dates scattered randomly from January 1, 2013 to July 27, 2015:

73015-1

So, I’m going to set up a PivotTable on the same worksheet and drag the Date category to both the Rows and Values quadrants:

73015-2

This gives me a list of every unique date and the number of times each appears in my list of 2,000. However, this may be a bit more granular that we would like, so I’m going to do some grouping. To do this, I just right click on any date in the list and choose Group:

73015-3

This brings up the Group dialog box, and I will select Months and Years:

73015-4

And now I have the dates broken down by year and sub-divided by month:

73015-5

Notice how there is no subtotal next to each year. This is a minor quirk in Excel. To correct this, right-click on the year and choose Field Settings. The Field Settings dialog box appears, and you can select Automatic:

73015-6

Now there is a subtotal for each year:

73015-7

You can also switch the Date and Years items in the Rows quadrant to see each month broken down by year:

73015-8

What can you do next?
Share this post with others that can benefit from it!
Leave a comment or reply below – let me know what you think!
Subscribe to this blog for more great tips in the future!
Check out my YouTube channel – click on the YouTube icon below!

Happy Excelling!

Related Post

Free Download!

Subscribe to Download Your FREE Copy of
"My 70+ Favorite Excel Keyboard Shortcuts" Today!

, , , , ,

No comments yet.

Leave a Reply