How to Group PivotTable Data by a Fiscal Quarter Starting in July in WPS Spreadsheets
Question details
The user wants to group PivotTable dates into fiscal quarters that begin in July instead of calendar quarters.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is building a PivotTable report from dated source data and needs July–September to appear as the first fiscal quarter.
- Observed behavior
- PivotTable date grouping uses calendar quarters by default, so July dates are shown in calendar Q3 rather than fiscal Q1.
Confirm the source range or Excel Table name that feeds the PivotTable and identify the date column you will use for the fiscal-quarter grouping.
Create a six-month shifted date helper column and group it by years and quarters
Use this when you need a reliable fiscal-quarter grouping for a July–June fiscal year without changing the original transaction dates.
Adding six months with EDATE moves an original July date into the next calendar year’s January, so calendar quarter grouping places July–September into Q1, October–December into Q2, January–March into Q3, and April–June into Q4.
In the source data, select the first data row of a new column and enter =EDATE(D2,6), replacing D2 with the cell that contains your original date.
Use the fill handle to copy the formula down the column so every source record has a shifted date. In an Excel Table, enter =EDATE([@TheDate],6) and let the calculated column fill automatically.
Click anywhere in the PivotTable, go to PivotTable Analyze > Change Data Source, extend the source range to include the new helper column, and then click Refresh.
Drag the helper date field into the PivotTable Rows or Columns area, right-click a date in that field, choose Group, select Years and Quarters, and confirm.
Group PivotTable dates by a July–June fiscal quarter in WPS Spreadsheets
Use the EDATE helper-column method in WPS Spreadsheets to shift source dates by six months, refresh the PivotTable source, and group the shifted date field by Years and Quarters for accurate July–June fiscal reporting.
- 1. Open the workbook in WPS Spreadsheets: Open the file that contains the source data and PivotTable, and locate the original date column.
- 2. Create the helper column: In a new column, enter =EDATE(D2,6) in the first data row and fill it down, or use =EDATE([@TheDate],6) in an Excel Table.
- 3. Refresh the PivotTable source: Click the PivotTable, open PivotTable Analyze > Change Data Source, include the helper column in the source range, and refresh the PivotTable.
- 4. Group the shifted date field: Add the helper date field to the PivotTable, right-click a date, choose Group, select Years and Quarters, and click OK.
Frequently Asked Questions
Why does a July–June fiscal year need a six-month date shift?
Adding six months moves an original July 1 date to January 1 of the following calendar year. When you group the shifted field by calendar quarters, July–September becomes fiscal Q1, October–December becomes fiscal Q2, and so on.
Does this method work when the source data is formatted as an Excel Table?
Yes. Use the calculated column formula =EDATE([@TheDate],6), update the PivotTable source to include the helper column, and group the shifted date field by Years and Quarters.
Can I start fiscal quarters in July without a helper column?
PivotTable date grouping is based on calendar quarters, so there is no built-in option to start a fiscal quarter in July. A shifted helper date column is the reliable way to force fiscal-quarter grouping.
Will the original date column still work in the PivotTable?
Yes. You can keep the original date field for detailed dates or labels, but you should use the shifted helper date field for fiscal-year and fiscal-quarter grouping.
Does WPS Spreadsheets support grouping PivotTable fields by Years and Quarters?
Yes. Right-click a date cell in the PivotTable, choose Group, select Years and Quarters, and confirm to group the dates.
