Show Missing Dates by Month in an Excel PivotChart
Question details
Display days without incident records in a monthly or year-to-date PivotChart.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Chart incident counts by date for a selected month or year-to-date period.
- Observed behavior
- The chart should include dates with no incidents without repeating every day of the year under each month.
Check that incident dates are stored as Excel dates and identify the date range the chart must cover. If incident dates include times, create a date-only column with =INT(cell) for the relationship.
Use a calendar table and the Excel Data Model
Use this approach to include dates with no records while filtering the chart to a month or year-to-date period.
Use actual calendar dates on the chart axis rather than combining separate month and day fields. This avoids creating combinations of dates that do not belong to the selected month.
On a new worksheet, enter a Date column containing every date in the required period, with no duplicates or blanks. Select the range and choose Insert > Table.
Format the incident data as a table with Insert > Table. Add the calendar and incident tables to the Data Model, then use Data > Relationships to link the incident date-only column to the calendar Date column.
Choose Insert > PivotTable and use the workbook's Data Model. Put the calendar Date field in Rows and an incident identifier in Values as a count. Select the PivotTable and choose Insert > PivotChart.
Right-click the calendar Date field in the PivotTable, open Field Settings > Layout & Print, and select Show items with no data. If needed, open PivotTable Options > Layout & Format and enter 0 for empty cells.
Use the calendar Date field's filter to select the desired month or a date range from the start of the year through the reporting date. Keep the actual Date field on the axis so only dates in that period are displayed.

Show Missing Dates in a WPS Spreadsheet Chart
Create a complete date list and count incidents for each day in WPS Spreadsheet, then chart the selected month or year-to-date range. This creates a regular chart rather than an Excel PivotChart.
- 1. Open the incident data: Open the workbook in WPS Spreadsheet and check that the incident dates are in a single column on an Incidents sheet.
- 2. List every date: On a new sheet, enter the first required date in A2, enter =A2+1 in A3, and fill the formula down through the end of the reporting period.
- 3. Count incidents by day: In B2, enter =COUNTIFS(Incidents!$A:$A,">="&A2,Incidents!$A:$A,"<"&A2+1), adjusting the sheet and column references to match your data. Fill the formula down beside the date list.
- 4. Create the chart: Select the date and count cells for the desired month or year-to-date period, then choose Insert > Chart and select a column or line chart.

Frequently Asked Questions
Why are dates with no incidents missing from my PivotChart?
A PivotChart based only on incident records has no source rows for those dates. Add a calendar table containing every required date, relate it to the incident table, and use its Date field on the axis.
How do I show only one month without repeating dates from other months?
Put the calendar's actual Date field on the axis and use its date filter to select the month. Avoid placing separate month and day fields on the axis if they create unwanted combinations.
What if my incident dates include times?
Add a date-only column to the incident table using =INT(cell), format it as a date, and relate that column to the calendar Date field through Data > Relationships.
Can I show a year-to-date period instead of a full year?
Yes. Use the calendar Date field's date filter to select the range from January 1 through the reporting date. Ensure the calendar table covers that entire range.




