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:


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:


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:


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


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


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:


Now there is a subtotal for each year:


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


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!

Free Download!

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

No comments yet.

Leave a Reply