Expert: Philip Buckley - 10/4/2009

Question
I have a column of dates (Month, Day and Year) and want to have excel tell me how many of dates there are in a given month.  That is how many dates show October as the month then how many show November etc.

Answer
1. For the column with the dates, make sure the column has a header labeled "Date" in cell "A1" with the data starting in cell "A2"
2. Label the next column over "Month" in cell "B1"
3. In cell "B2" type "=MONTH(A2)"

This will put the month in numerical form into cell "B2"

4. Copy cell "B2"
5. Paste down starting in cell "B3" and ending with the last date from column "A"

This will put the month in numerical form into column "B" for every date in column "A"

6. Click on the data with your mouse.
7. Select "Data" from the main menu
8. Select "PivotTable and PivotChart Report..."

This will bring up the "PivotTable and PivotChart Wizard"

9. Select "Next"
10. In the range section select columns "A" and "B"
11. Select "Next"
12. Select "Layout"
13. Drag the "Month" tile to the "Row" section
14. Drag the "Date" tile to the "Data" section

The "Data" section should now say "Count of Date"  If it does not, double click on this tile and select "Count" and then "OK"

15. Select "OK"
16. Select Finish

This will give you a PivotTable that counts the number of dates for each month.