4-4-5 Calendar

[twdavis]

I need a function to look at a date and decide what period it falls in.


We use a 4-4-5 Calendar and I would like to know how to create vba code or a function that we can use to check invoice dates and give the correct period that the sale should fall in. That way we can pivot by period.

 

[Jonmo1]

OK, Taking a guess, a 4-4-5 year is what...

period 1 and 2 are 4 weeks
period 3 is 5 weeks
period 4 and 5 are 4 weeks
period 6 is 5 weeks
etc...

Is that right?

that totals 364 days. But there are 365 days in a year (366 on a leap year)

What period do Dec 31st (and 30th on leap year) belong to?

 

[twdavis]

Yes that is correct

period 12

 

[Jonmo1]

Perhaps

=MATCH(A1-DATE(YEAR(A1),1,1),{0,29,57,92,120,148,183,211,239,274,302,330})

 

[twdavis]

That works great

Thank you very much

 

[pdowg881]

Is there a way to adapt this to a 4-5-4 format that begins in October (actual days are Sep 27-Oct 24) that would work with any year? There's also the 364 day/missing week issue that comes up every 7 years.

 

[Jonmo1]

You wold just have to adjust the numbers in the constant array
=MATCH(A1-DATE(YEAR(A1),1,1),{0,29,57,92,120,148,183,211,239,274,302,330})

They're each just the first day # of each period.
Start with 0 for the the first day of the year.
first 4 weeks is 7*4=28, so 29 is the first of the 5th week.
Then another 4 weeks (now 8 weeks) is 7*8 = 56, so 57 is the first of the 9th week.
Then another 5 weeks (now 13 weeks) is 7*13 = 91, so 92 is the first day of the 14th week.
Etc.

 

[Sunray_Minor]

Hello

Can this super formula be modified to work over different years?

My company works 4-4-5 week accounting periods April to March.

 

[men5j2s]

So I understand from this that if I do the following formula, it will give me the week number in the year (retail year, 31/1/16-29/1/17).

=MATCH(A1-DATE(YEAR(A1),1,1),{30,36,43,50,57,64,71,78,85,92,99,106,113,120,127,134,141,148,155,162,169,176,183,190,197,204,211,218,225,232,239,246,253,260,267,274,281,288,295,302,309,316,323,330,337,344,351,358,365,6,13,20,27})

But it is not quite working; have I done it correctly?

The aim is to get week numbers (from a date) for a 4-4-5 year (4 weeks then 4 weeks then 5 weeks per month, and repeat).