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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ml123 - 19 Mar 2008 12:31 GMT

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?

Reply to this Message

Roger Govier - 19 Mar 2008 13:48 GMT

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)

Signature

Regards
Roger Govier

> 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?

Reply to this Message

Adilson Soledade - 19 Mar 2008 13:58 GMT

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.

Signature

Adilson Soledade

> 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?

Reply to this Message