How to List Months and Count Dates by Month in WPS Office Spreadsheets
Question details
The user needs to extract month and year values from a date column and count how many records occur in each month.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has a date column and needs a monthly summary showing each month and the number of records that fall in that month.
- Observed behavior
- The user wants to create a list of months from a date column and count the number of dates for each month.
Make sure the date column contains real date values instead of text. If the dates are stored as text, convert them to Date format before grouping or counting so month results are accurate.
Create a PivotTable to Group Dates by Month and Count Records
Best for quick monthly summaries without formulas. This method is recommended for most users.
A PivotTable can automatically group a date field by years and months and calculate a count for each group. This keeps the original data unchanged.
Click any cell in the data range or select the column that contains the dates, including the header.
Go to the Insert tab and click PivotTable. In the dialog, choose a location for the PivotTable and click OK.
In the PivotTable Fields pane, drag the date field to the Rows area and also to the Values area. The Values area should show Count of the date field.
Right-click any date in the PivotTable row labels, choose Group, select Years and Months, then click OK.
List Unique Months and Count Dates with TEXT, UNIQUE, and COUNTIF Formulas
Use this method when you need a live formula-driven list that updates automatically or prefer to keep a monthly summary beside the source data.
Use WPS Office Spreadsheets to List Months and Count Dates by Month
WPS Office Spreadsheets can directly build a monthly count report from your date column. Use a PivotTable to group dates by years and months, or apply TEXT and COUNTIF formulas for a live summary, and keep full compatibility with Microsoft Excel .xlsx files.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, open the workbook that contains the date column, and click any cell in the data range.
- 2. Insert a PivotTable: Go to the Insert tab, click PivotTable, and confirm the data range and output location in the dialog.
- 3. Add the date field to Rows and Values: In the PivotTable Fields pane, drag the date field to Rows and then to Values so it shows as Count.
- 4. Group by years and months: Right-click a date in the PivotTable row labels, select Group, choose Years and Months, and click OK.
- 5. Save or export the report: Save the workbook as .xlsx or export the summary to PDF from the Menu tab.
Frequently Asked Questions
Why doesn't my date field group by month in a PivotTable?
Usually the date column contains blank cells or dates stored as text. Select the column and set the format to Date or convert text dates with Data > Text to Columns, then refresh the PivotTable and group again.
Can WPS Spreadsheets use the Excel formula =LET(x,UNIQUE(EOMONTH(A2:A8,0)),y,COUNTIFS(...),HSTACK(...))?
WPS Office supports many Excel functions, but support for newer dynamic array functions can vary by version. If LET, HSTACK, or UNIQUE is unavailable, use a helper column with TEXT and COUNTIF, or create a PivotTable for the same result.
How do I count only dates in a specific month?
Use =COUNTIFS(date_range,">="&DATE(year,month,1),date_range,"<"&DATE(year,month+1,1)). Replace date_range, year, and month with your actual range and values.
Will the monthly count update automatically when I add new dates?
Formula-based summaries update automatically. If you use a PivotTable, right-click the PivotTable and choose Refresh after adding data, or convert the source range to a Table so new rows are included more easily.
