Months from Week numbers

[KevinB]

I am trying to get the months from a date or week number

The part that is complicating me is that I need the months to be in 4wk-4wk-5wk for the year, each Qtr ends with a 5 week month. I cant seem to figure out how to get this to work. I would think that there would be a way to get the month returned from the week number, ie. 1-4=Jan, 5-8=Feb, 9-13=Mar.

Am I missing something simple here?

 

[Luke M]

Hi KevinB,

Assuming your input is a weeknumber, (aka, 1-52), then this should work:
=TEXT(DATE(1,LOOKUP(MOD(A2-1,13)+1,{1,5,9},{1,2,3})+3*INT((A2-1)/13),1),"mmm")

The key to the formula is the LOOKUP doing the MOD and INT calculations. It's converting the numbers 1-13 into correct month, and then figuring out which quarter it's in.

 

[bobhc]

You could use the upload to construct a lookup.

 

[KevinB]

Luke,

I keep getting a #Ref! error, I have a table named Lookup and it is using that instead of the formula. I tried selecting the formula instead of the table name but it would just revert back to the table when I hit enter to finish editing the formula.

After I changed the table name to Lookup1, it had the correct 4-4-5 wk/month pattern but it was a week early

 

[Luke M]

First, I would strongly suggest taking it as a warning not to assign names (tables, named ranges, etc.) that are also function names, as it can cause issues.

As for the pattern, can you explain what you mean? You originally stated that weeks 1-4 = Jan, which is what I'm seeing with formula:


Also, it may be helpful if you could upload your workbook, so we can better see what's going on.

 

[KevinB]

Didn't really think through the name of the Table, just getting into Tables and Structured References, makes sense though. It was a lookup table so that was the most logical name, its changed now.

My Accounting Dept uses a numbering system were the end of the Qtr has to have the 5 week month, so each Qtr has 4-4-5 weeks in it.

Top row is what i need the months to be, row 2 is your formula, row 3 week number, and row 4 start date of week.

 

[Luke M]

Based on your file, question/assumption above

Assuming your input is a weeknumber, (aka, 1-52), ...

is no longer true. You have week numbers going up to 53, and will sometimes have a weeknumber 54 (the year 2000 and 2028 both have 54 weeks). As a 4-4-5 calendar only has 52 weeks, how do you want to handle the extra weeks? E.g., what are the rules for handling the extra weeks?

 

[Luke M]

I think the issue is how you're currently calculating the weeknumber. Try changing the formula in cell B3 to this:
=WEEKNUM(B4,10+WEEKDAY(DATE(YEAR(B4),1,1),2))
which will make it so that the first Sunday of the year will be counted as week 1. This should get things mostly in line (except for the odd 54 week years, as noted above)

 

[KevinB]

Thanks Luke that works great!