4-4-5 Week Finance Calendar Identify

[Shark00]

Hey, we have a 4-4-5 week finance period structure.
Our Finance year runs Jan - Dec with a 4 week 4 week 5 week quarter
I'm building a model where the first month will change, based on our finance structure I want excel to tell me if it's a 4 or 5 week month, so to return "4" or "5" based on the month selected... any help is greatly appreciated

Example:
Model starts in March, I want it to let me know March is a 5 week month

 

[Phuoc]

Try this:

Book1
	A	B
1	Month:	2
2	Number of weeks:	4
Sheet1

Cell Formulas
Range	Formula
B2	B2	=4+(MOD(B1,3)=0)

Cells with Data Validation
Cell	Allow	Criteria
B1	List	1,2,3,4,5,6,7,8,9,10,11,12

 

[Shark00]

That seems to only be returning "4"?

So January, February, April, May, July, August, October and November are 4 week months, March, June, September and December are 5 week months

If the source cell, which is a date, has a particular month in it, I'd like it to give me a 4 or 5, depending on which month is in the source cell...

 

[Phuoc]

If the source cell, which is a date,

Try change to:

=4+(MOD(MONTH(B1),3)=0)

Or:

=IF(OR(MONTH(B1)={3,6,9,12}),5,4)

 

[Alex Blakenburg]

You might want to consider using a lookup table using the mont number (or better still a calendar table as you would do in Power BI), since every 5 or 6 years you will have an extra 5 week month to catch up for the fact that 4-4-5 comes to 364 days.