Month Number based on Week Number

[Kevs_Lloren07]

Hello,

Hope you are all doing well.

I would like to know if it is possible to customize the month number based on week number.

Our fiscal week starts on a Wednesday and ends on a Tuesday.

For example, December 30, 2020 until January 5, 2021 should be the week 1 and January 20 up to January 26, 2021 should be the week 4. These week number should be in " month 1 " then the week 5 should be " month 2 " already.

I've got the week number covered already, it's just the month number I can't figure out.

Thank you!

 

[jasonb75]

What information do you have in the sheet as a reference point? If you have a date to produce the week number then it would be easier to get the month from the same date than it would to calculate from the week number.

For example, with a date in A1 this formula would provide the correct month number on the assumption that it should be based on the last day of the week (which is the only logical pattern that fits the information in your post).

=MONTH(CEILING(A1-3,7)+3)

 

[Kevs_Lloren07]

What information do you have in the sheet as a reference point? If you have a date to produce the week number then it would be easier to get the month from the same date than it would to calculate from the week number.

For example, with a date in A1 this formula would provide the correct month number on the assumption that it should be based on the last day of the week (which is the only logical pattern that fits the information in your post).

=MONTH(CEILING(A1-3,7)+3)

Hello @jasonb75,

Thanks for your response. I've attached an image of the sample data I have. Column B is the date and the Column C is the Week Number I set using the formula =WEEKNUM(B2,11). I would like to have the month number on the column D.

For week 18, the month number should be 5 already but some of them shows 4. Month number should've started from April 26.

 

[jasonb75]

I think it's your week numbers that are incorrect. Week 18 should start on the April 28 based on the information in post 1.

Our fiscal week starts on a Wednesday and ends on a Tuesday

Your formula,

=WEEKNUM(B2,11)

is for a week starting on Monday. You would need to use

=WEEKNUM(B2,13)

for a Wednesday to Tuesday week.

The formula that I suggested will need a slight change though, because you have times as well as dates there could be some unwanted rounding up with the original formula which this will prevent.

=MONTH(CEILING(INT(A1)-3,7)+3)

 

[Kevs_Lloren07]

Yeah, my bad. It was for a different report. for the one I'm trying to create starts on a Monday and ends on a Sunday.

Anyway, can you tell me the logic for this formula "=MONTH(CEILING(INT(A1)-3,7)+3)". I've applied it on the previous image I've attached. Some cell returns 4 and the others are 5.

I've changed it to "=MONTH(CEILING(INT(B2)-3,7)+3)"

 

[jasonb75]

Ceiling rounds the date up to the end of the week, by default it would round to the next Saturday on, or after the date in the cell.

For any other day of the week you need to subtract the weekday, round up the remainder, then add the weekday back on afterwards. For a week ending Tuesday, you subtract 3, round up, then add 3.

For other days of the week, it would be 1 for Sunday, 2 for Monday, etc. Note that this is the end of the week, not the start.

 

[Kevs_Lloren07]

Just tried it @jasonb75 and it works. Super thanks for your time! <3