When you use date grouping (by months) in one Pivot Table report it affects other Pivot Table’s date reporting from the same data. There is a work around to allow you to have daily, monthly and quarterly Pivot Table reports.

You can add extra columns to your data to enable separate date-based reporting. In the image below we have added two columns to the right to handle monthly reporting and quarterly reporting. The Quarter column assumes a July – June financial year.

The formula for cell H2 is

=DATE(YEAR(A2),MONTH(A2),1)

This normalises the date from column A to the first day of the month and allows for monthly reporting.

The formula for cell I2 is

="Q"&CHOOSE(MONTH(A2),3,3,3,4,4,4,1,1,1,2,2,2)

The CHOOSE function saves having to do multiple nested IF functions to work out the Quarter number. The MONTH(A2) at the start of the CHOOSE function returns a number from 1 to 12. The numbers that follow represent the 12 sequential quarter numbers for each month from January to December. Months 1 to 3 are all 3; months 4 to 6 are all 4; months 7 to 9 are all 1 and months 10 to 12 are all 2. The CHOOSE function uses the first entry (a number from 1 to 12) to choose from the following 12 entries and returns that number from the sequence.

Now you can use the Date, Month and Quarter columns (fields) to create three separate date-based Pivot Table reports from the same data.