Formula to figure out which month a week of a year belongs to?

[rdotson]

Does anyone have a formula to figure out which month a week of a year belongs to? For example, this current week (week of June 22nd, 2014; for this application weeks start on Sunday) is week 26 of the year. It is a week in June of course.

The complicated part is weeks where the month changes over mid-week. I would like it so that a week is considered a part of the month that it has more days in- for example, next week (The week starting on Sunday, June 29th) would be considered a week in July because it has 5 days in July vs. only 2 in June.

Is this possible?

Here are the details of my project if needed-
There are a bunch of workbooks titled "Week __", where the blank is the number of the week of the year. These workbooks have weekly summary data in them, and I would like to create a monthly workbook that pulls the data from these weekly workbooks. I would like the calculations to happen automatically upon opening this monthly workbook, and I need a way to figure out which weeks are a part of a certain month so that I can reference the correct data. I know this will involve visual basic code but I'm struggling with the formula/calculation itself.

 

[VBA Geek]

try

<b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">D4</th><td style="text-align:left">=MODE(<font color="Blue">MONTH(<font color="Red">ROW(<font color="Green">INDIRECT(<font color="Purple">C4-WEEKDAY(<font color="Teal">C4,1</font>)+1 & ":" & C4-WEEKDAY(<font color="Teal">C4,1</font>)+7</font>)</font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

Excel 2013
	C	D
4	29/06/2014	7
Sheet2

 

[BiocideJ]

Mine is a different take on your question using VBA.
creating a function that can be called directly from VB or via an Excel Formula
It takes two arguments. The first is the week number and the second OPTIONAL argument is the year to be considered (default is year of TODAY)

You can call it either as an Excel formula
e.g. =Week2Month(26,2014)

or from a subroutine in VBA as I show in the testW2M Sub.


The output is an integer representation of the Month (i.e. 1, 2, 3, ... , 12)

Sub testW2M()
    myMonth = Week2Month(1)
End Sub
 
Public Function Week2Month(weekNum As Integer, Optional yearNum As Long = -1) As Integer
On Error GoTo ErrHand
Dim Week1Begin As Date
    If yearNum = -1 Then yearNum = Year(Date) 'current year
    If weekNum < 1 Or weekNum > 52 Then GoTo ErrHand
    Week1Begin = DateValue("1/1/" & yearNum) - 7    '-7 is so week1 is first week in Jan
    
    Week2Month = Month(DateAdd("d", weekNum * 7, Week1Begin))
    
    Exit Function
ErrHand:
    Week2Month = CVErr(xlErrValue)  'outputs #VALUE!
End Function

 

[rdotson]

Sorry, left out one important part. The input to the formula needs to be the week number of the year, not a date. I'm going to be looking at file names and they are saved as "Week 1", "Week 2", etc.

 

[rdotson]

Mine is a different take on your question using VBA.
creating a function that can be called directly from VB or via an Excel Formula
It takes two arguments. The first is the week number and the second OPTIONAL argument is the year to be considered (default is year of TODAY)

You can call it either as an Excel formula
e.g. =Week2Month(26,2014)

or from a subroutine in VBA as I show in the testW2M Sub.


The output is an integer representation of the Month (i.e. 1, 2, 3, ... , 12)

Sub testW2M()
    myMonth = Week2Month(1)
End Sub
 
Public Function Week2Month(weekNum As Integer, Optional yearNum As Long = -1) As Integer
On Error GoTo ErrHand
Dim Week1Begin As Date
    If yearNum = -1 Then yearNum = Year(Date) 'current year
    If weekNum < 1 Or weekNum > 52 Then GoTo ErrHand
    Week1Begin = DateValue("1/1/" & yearNum) - 7    '-7 is so week1 is first week in Jan
    
    Week2Month = Month(DateAdd("d", weekNum * 7, Week1Begin))
    
    Exit Function
ErrHand:
    Week2Month = CVErr(xlErrValue)  'outputs #VALUE!
End Function

Wow, impressive. That looks like it will work well for what I'm trying to do. Big thank you, and I'll come back if I have any issues with it.

 

[BiocideJ]

OK.
I tested it but not extensively.

It definitely worked for the dates you gave and the few I tested in 2013 and 2014.

 

[VBA Geek]

Sorry, left out one important part. The input to the formula needs to be the week number of the year, not a date. I'm going to be looking at file names and they are saved as "Week 1", "Week 2", etc.

np, try it like this

where: A1 is the four digit year
A2 is the weeknumber

=MODE(MONTH(ROW(INDIRECT(DATE(A1,1,A2*7-2)-WEEKDAY(DATE(A1,1,3))-1 & ":" & DATE(A1,1,A2*7-2)-WEEKDAY(DATE(A1,1,3))+5))))

 

[BiocideJ]

To the OP:
Hopefully you check back on this thread as I realized last night that I had made a logical error in the code I had posted.
Please use the code below which is corrected.

Sub testW2M()
    myMonth = Week2Month(27)
    If Not IsError(myMonth) Then MsgBox myMonth
End Sub

Public Function Week2Month(weekNum As Integer, Optional yearNum As Long = -1) As Variant
On Error GoTo ErrHand
Dim Week0Mid As Date
    If yearNum = -1 Then yearNum = Year(Date) 'current year
    If weekNum < 1 Or weekNum > 52 Then GoTo ErrHand
    
    Week0Mid = DateValue("1/1/" & yearNum) - Weekday(DateValue("1/1/" & yearNum), vbThursday)
    
    Week2Month = Month(DateAdd("d", weekNum * 7, Week0Mid))
    Exit Function
ErrHand:
    Week2Month = CVErr(xlErrValue)  'outputs #VALUE!
End Function