Pivot Table - Need to group transactions by non standard month

Hi Guys,

I have a pivot table, and I need to summarise the amounts in this pivot table based on non standard month start and end dates.

Please see my attached spreadsheet for an example of the data, my period start and end dates and the pivot table I am aiming to achieve.

Any help would be greatly appreciated.

Thanks
Dummy.xlsx

Am I getting you right, that all you want to do is to group your dates?

You can simply select the cells you want to group in the pivot, right click and select group. Like this you can for example create a group that runns from 12.02.2009-06.10.2009.

I might have gotten your question wrong though, please let me know.

Cheers
Fab

Hi,

Thats what I have done so far, but I want each column to show totals for a specified date range (which is referenced from another sheet).

I want to use this pivot table to aggregate data which I will then reference to in an output sheet.

Thanks

I think I have a solution. However, it uses a calculation on the raw data, rather than elegantly using some pivot functionality..

1) specify your period ranges in a table (example below)

period	start	end
1	02.03.2008	05.04.2008
2	06.04.2008	26.05.2009
3	27.05.2009	19.10.2012

2) Next we add boolean fields for each period to the rows, checking whether the entry is part of the period

date (original data)	period 1	period 2	period 3	period (will be used in step 3)
03.03.2008	1	0	0
10.04.2009	0	1	0
30.05.2009	0	0	1
11.04.2009	0	1	0

The cells use a simple comparison: =IF(AND((DateField>StartOfRespPeriod);(DateField<EndOfRespPeriod));1;0)

3) in the last column we can now display the period with: =MATCH(1;RangeOfPeriodCheckFields;0)

date (original data)	period 1	period 2	period 3	period
03.03.2008	1	0	0	1
10.04.2009	0	1	0	2
30.05.2009	0	0	1	3
11.04.2009	0	1	0	2

Of course, this implicitly solves the grouping problem...


Cheers
Fab

Hi,
In the income table : new column to calculate the corresponding periods based on Periods sheet (2 types of formula)
Insert a pivot table including those columns and insert the needed fields
Hope this help
Best regards