Excel formula to get Month # from Week#.

pedie

Well-known Member
Joined
Apr 28, 2010
Messages
3,875
Please help me with Excel formula to get Month # from Week#.
Week 31 = Month 7

i tried this but is not accurate =MONTH(DATE(H2,1,H2*7-2))


thanks in advance.

<TBODY>
</TBODY><COLGROUP><COL></COLGROUP>
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Not totally accurate, as week1 does not necessarily start on 1st Jan, but does this do what you want?

=MONTH(DATE(YEAR(TODAY()),1,H2*7-2))
 
Upvote 0
I was not sure what in your H2 so if you have text format maybe:
Sheet1

*AB
1Week 1Month 1
2Week 2Month 1
3Week 3Month 1
4Week 4Month 1
5Week 5Month 1
6Week 6Month 2
7Week 7Month 2
8Week 8Month 2
9Week 9Month 2
10Week 10Month 3
11Week 11Month 3
12Week 12Month 3
13Week 13Month 3
14Week 14Month 4
15Week 15Month 4
16Week 16Month 4
17Week 17Month 4
18Week 18Month 4
19Week 19Month 5
20Week 20Month 5
21Week 21Month 5
22Week 22Month 5
23Week 23Month 6

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:75px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas
CellFormula
B1="Month "&MONTH(((RIGHT(A1,2)+0)*7)+DATE(12,1,2)-7)

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
 
Upvote 0

Forum statistics

Threads
1,211,962
Messages
6,105,105
Members
447,947
Latest member
OX_2005

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