Year into 13 periods

[Westie102]

Hello,

I would be grateful for some assistance, please.

My company uses 13 'periods' of 4 weeks each for it's reporting. How on earth can I get excel to be able to produce reports using "normal" dates from the data source, but then somehow referencing them against the year/period/week?

I can easily work from a fixed date, so for this financial year 2018/19, for example, Week 1 of Period 1 started on 1st April 2018.
Every 4 weeks we start a new period, so 29th April 2018 became week 1 of Period 2, 27th May became week 1 of period 3 and so on.
Next financial year 2019/20 will start on 31st March 2019 being Week 1 of period 1 and continue the same way.

The data spreadsheet uses 'normal' dates, but I need to be able to produce graphs and charts (hopefully pivot tables!) against Years and Periods and weeks within those periods.

I hope that makes sense. I have spent ages researching, but just keep coming up blank and so I need to ask for help

Many thanks in anticipation

 

[6StringJazzer]

Let's say you put the date that the year starts in A1. The date of interest is in A5. For the date of interest:

The Period Number is
=INT((A5-A1)/28)+1

The week number within the period is
=INT(MOD(A5-$A$1,28)/7)+1

For each date I would have either two cells with these numbers, or possibly one cell that combines them to use in charts (i.e., "Period 11 Week 1" or just "11-1")

Does that move you along?

13 periods of 4 weeks is still only 364 days. How do you manage the backwards drift each year?

 

[DRSteele]

If I understand correctly, this might help. The construction in ColE is done in such a way that it is sortable in ascending or descending order up to 999 periods. The same kind of thing would have to be done for years were they to tick over 9. PivotTables should handle this type of data adequately.

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]

Book1
	A	B	C	D	E
1	date	fiscal year	period	week	datadate
2	Sun 4/01/18	1	1	1	y1-p001-w1
3	Mon 4/09/18	1	1	2	y1-p001-w2
4	Thu 4/19/18	1	1	3	y1-p001-w3
5	Wed 4/25/18	1	1	4	y1-p001-w4
6	Sun 4/29/18	1	2	1	y1-p002-w1
7	Sun 5/27/18	1	3	1	y1-p003-w1
8	Sun 6/24/18	1	4	1	y1-p004-w1
9	Sun 7/22/18	1	5	1	y1-p005-w1
10	Sun 8/19/18	1	6	1	y1-p006-w1
11	Sun 9/16/18	1	7	1	y1-p007-w1
12	Sun 10/14/18	1	8	1	y1-p008-w1
13	Sun 11/11/18	1	9	1	y1-p009-w1
14	Sun 12/09/18	1	10	1	y1-p010-w1
15	Sun 1/06/19	1	11	1	y1-p011-w1
16	Sun 2/03/19	1	12	1	y1-p012-w1
17	Sun 3/03/19	1	13	1	y1-p013-w1
18	Sun 3/31/19	2	14	1	y2-p014-w1
19	Sun 4/28/19	2	15	1	y2-p015-w1
Sheet7

Cell Formulas
Range	Formula
B2	=ROUNDUP((A2-$A$2+1)/364,0)
B6	=ROUNDUP((A6-$A$2+1)/364,0)
C2	=ROUNDUP((A2-$A$2+1)/28,0)
C6	=ROUNDUP((A6-$A$2+1)/28,0)
D2	=INT(MOD(A2-$A$2,28)/7)+1
D6	=INT(MOD(A6-$A$2,28)/7)+1
E2	="y"&B2&"-p"&TEXT(C2,"000")&"-w"&D2
E6	="y"&B6&"-p"&TEXT(C6,"000")&"-w"&D6
A6	=A2+28

[/FONT]

 

[Westie102]

Awesome, thank you both so much for your help - I shall set to work using your advice imminently, most appreciated!

 

[Westie102]

I'm not sure how the accountants work out the 364 day issue.. never really thought about it before - I will have to ask now you've prompted my curiosity!