Excel formula to get Month # from Week#.

[pedie]

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>

 

[theBardd]

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))

 

[Robert Mika]

I was not sure what in your H2 so if you have text format maybe:
Sheet1

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

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

Spreadsheet Formulas

Cell Formula 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

 

[pedie]

Thanks guys....both works!