Group Excel data in varying length financial periods 4/5 weeks
 

Need to summarised order/invoice data by financial period. This Financial
Year we have 12 Financial Periods each involving the following number of
weeks: P1 = 4, P2 = 4, P3=5, P4=4, P5=4, P6=5, P7=4, P8=4, P9=5, P10=4,
P11=4, P12=5. In MS Excel is there a way of Grouping Dates to reflect these
varying lenght periods so we can use the Group in Pivot Tables?

Group Excel data in varying length financial periods 4/5 weeks
 

Hi

You will need to add an extra column to your source data, and have a formula
in there to allocate the row to one of the Financial periods
Include this column in your source data for the PT, and drag the filed to
the Row or Column area

In order to use the formula column, set up a list of your dates in a column
on another sheet in cells A1:A13
A1 will be 0, A2 will be the end of the first period, A3 end of second
period etc.
In B1:B12 enter the numbers 1 to 12

In your new column in the source table (assuming the transaction date is in
column A of that sheet) enter
=VLOOKUP(A2,Sheet2!$A$1:$B$13,2,1)

--
Regards
Roger Govier

"ml123" wrote in message
...
Need to summarised order/invoice data by financial period. This Financial
Year we have 12 Financial Periods each involving the following number of
weeks: P1 = 4, P2 = 4, P3=5, P4=4, P5=4, P6=5, P7=4, P8=4, P9=5, P10=4,
P11=4, P12=5. In MS Excel is there a way of Grouping Dates to reflect
these
varying lenght periods so we can use the Group in Pivot Tables?

Group Excel data in varying length financial periods 4/5 weeks
 

I tought in then following aproach for your demand:
1. Creation of the following table:
Column A Column B
4 P1
8 P2
13 P3
17 P4
21 P5
26 P6
30 P7
34 P8
39 P9
43 P10
47 P11
52 P12
2. If the table above is, for example, in the range $A$1:$B$8, you could use
the following function in your database to achieve the match period for one
date =VLOOKUP(WEEKNUM(Date,$A$1:$B$8,2). Use it as to a new column insert or
to right of your data base.
3. So, you could add this new field to your PivotTable and use it to group
your data.

--
Adilson Soledade


"ml123" wrote:

Need to summarised order/invoice data by financial period. This Financial
Year we have 12 Financial Periods each involving the following number of
weeks: P1 = 4, P2 = 4, P3=5, P4=4, P5=4, P6=5, P7=4, P8=4, P9=5, P10=4,
P11=4, P12=5. In MS Excel is there a way of Grouping Dates to reflect these
varying lenght periods so we can use the Group in Pivot Tables?