Convert WEEKNUM() to MONTH()

jboulton98

New Member
Joined
Aug 11, 2014
Messages
9
I am trying to find a formula that will return the month number from a given week number. Here are some things that I have defined for my purposes:
  • The week begins on Sunday
  • A month begins on the Sunday of the week that contains the 1st of the given month

For example, I have defined that the first day of January this year is 29-Dec-2013 since 01-Jan-2014 is a Wednesday. Similarly, the first day of November is 26-Oct-2014 since 01-Nov-2014 is a Saturday.

Thanks in advance for your help.
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
OK its accurate within a normal value of normal. I've never heard of that style of function

Best wishes
 
Upvote 0
I think one of these formulas will do what you want.

Assuming the year is hard-coded in the formula...
------------------------------------------------------------------
="1/1/2014"-WEEKDAY("1/1/2014")+7*(A1)-WEEKDAY("1/1/2014"-WEEKDAY("1/1/2014")+7*(A1))+1

Assuming the year is stored in B1..
------------------------------------------------
=("1/1/"&B1)-WEEKDAY(("1/1/"&B1))+7*(A1)-WEEKDAY(("1/1/"&B1)-WEEKDAY(("1/1/"&B1))+7*(A1))+1

Note: You might have to format the cell with the Date format of your choice as I think these formulas will return the date's serial number.
 
Last edited:
Upvote 0
Perhaps I wasn't clear in my OP. My apologies.

Column A is a date
Column B is the formula =WEEKNUM(A1)
Column C should be the formula to return the numeric month number (1, 2, 3, ..., 12) based on column A or B (whichever is easiest) and the criteria given in my OP.

For example:
If A1=30-Dec-2013, then B1=1 and C1=1
If A1=10-Aug-2014, then B1=33 and C1=8
If A1=26-Oct-2014, then B1=44 and C1=11
etc.
 
Upvote 0
Perhaps I wasn't clear in my OP. My apologies.

Column A is a date
Column B is the formula =WEEKNUM(A1)
Column C should be the formula to return the numeric month number (1, 2, 3, ..., 12) based on column A or B (whichever is easiest) and the criteria given in my OP.

For example:
If A1=30-Dec-2013, then B1=1 and C1=1
If A1=10-Aug-2014, then B1=33 and C1=8
If A1=26-Oct-2014, then B1=44 and C1=11
etc.

Oh, I thought you were trying to go the other way. Okay, these formulas appear to work (although I cannot help thinking there are more streamlined formulas available)...

B1: =MOD(WEEKNUM(A1),52)

C1: =MOD(MONTH(A1)+(MONTH(A1-DAY(A1)+1)=MONTH(A1)),12)
 
Upvote 0
My solution is a little long because I'm not good at streamlining formulas, just getting the results I need...

First of all, start in row 2:

Column A: Date
Column B: =WEEKNUM(A2,1)
Column C: =IF(AND(MONTH(A2)=12, MONTH(A2+6)<MONTH(A2),WEEKDAY(A2,1)=1), MONTH(A2+6), IF(AND(MONTH(A2+6)>MONTH(A2), WEEKDAY(A2,1)=1), MONTH(A2+6), IF(MONTH(A2+6)=C1, MONTH(A2+6), MONTH(A2))))

<month(a2),weekday(a2,1)=1),month(a2+6),if(and(month(a2+6)><month(a1),weekday(a1,1)=1),month(a1+6),if(and(month(a1+6)>
After you run the formula down, manually type the correct month number into cell C1 (otherwise you'll have a #REF error for the first week)

Like I said, probably a lot more streamlined ways to do it, but I tried the above and it didn't work for me so I figured I'd give another option.</month(a1),weekday(a1,1)=1),month(a1+6),if(and(month(a1+6)></month(a2),weekday(a2,1)=1),month(a2+6),if(and(month(a2+6)>
 
Last edited:
Upvote 0
Oh, I thought you were trying to go the other way. Okay, these formulas appear to work (although I cannot help thinking there are more streamlined formulas available)...

B1: =MOD(WEEKNUM(A1),52)

C1: =MOD(MONTH(A1)+(MONTH(A1-DAY(A1)+1)=MONTH(A1)),12)
That doesn't seem to be it, either. I've actually solved the week number piece of the puzzle already. I'm using a simple IF function based on the date: =IF(WEEKNUM(A1)>52,WEEKNUM(A1)-52,WEEKNUM(A1))
This takes care of the fact that WEEKNUM(29-Dec-2013) would return 53.

The piece I can't figure out is getting the correct month number. Your C1 formula isn't right. Here's how you can test:
  1. In A1, enter the date 29-Dec-2014
  2. In A2, enter the formula =A1+7
  3. Fill down to row 52
  4. In B1, enter the formula =IF(WEEKNUM(A1)>52,WEEKNUM(A1)-52,WEEKNUM(A1)) your B1 formula works except for week 52, which shows the result of 0
  5. Fill down to row 52
  6. The results of the month number formula I'm looking for should give 1,1,1,1,2,2,2,2,3,3,3,3,3,4,4,4,4,5....etc
 
Upvote 0
Keep having parts of the formulas deleted or changed... Lemme try this:

IF(AND(MONTH(A2)=12,MONTH(A2+6)"less than sign"<month(a2),weekday(a2,1)=1),month(a2+6),if(and(month(a2+6)>MONTH(A2),

WEEKDAY(A2,1)=1),MONTH(A2+6),IF(AND(MONTH(A2+6)"greater than sign"MONTH(A2),

WEEKDAY(A2,1)=1), MONTH(A2+6),

IF(MONTH(A2+6)=C1, MONTH(A2+6),MONTH(A2))))</month(a2),weekday(a2,1)=1),month(a2+6),if(and(month(a2+6)>
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top