# Get Monthday from week of month.

#### minmark

##### New Member
Dear Gents,

For example, if Sounday is first day.
I need to get 4 monthday from week of month. (1,2,3,4)

for week two in this month, I need to get (5,6,7,8,9,10,11)
........

So if I know the week of this month. How can I get 1st day of this week or last of this week?
Thanks.

The week of month codes are following.
Code:
``````Function WeekOfMonth(selDate As Date)
Dim DayOfFirst As Integer
Dim StartOfWeek2 As Integer
Dim weekNum As Integer
DayOfFirst = Weekday(DateSerial(Year(selDate), Month(selDate), 1), vbSunday)
StartOfWeek2 = (7 - DayOfFirst) + 2
Select Case selDate
Case DateSerial(Year(selDate), Month(selDate), 1) _
To DateSerial(Year(selDate), Month(selDate), StartOfWeek2 - 1)
weekNum = 1
Case DateSerial(Year(selDate), Month(selDate), StartOfWeek2) _
To DateSerial(Year(selDate), Month(selDate), StartOfWeek2 + 6)
weekNum = 2
Case DateSerial(Year(selDate), Month(selDate), StartOfWeek2 + 7) _
To DateSerial(Year(selDate), Month(selDate), StartOfWeek2 + 13)
weekNum = 3
Case DateSerial(Year(selDate), Month(selDate), StartOfWeek2 + 14) _
To DateSerial(Year(selDate), Month(selDate), StartOfWeek2 + 20)
weekNum = 4
Case DateSerial(Year(selDate), Month(selDate), StartOfWeek2 + 21) _
To DateSerial(Year(selDate), Month(selDate), StartOfWeek2 + 27)
weekNum = 5
Case DateSerial(Year(selDate), Month(selDate), StartOfWeek2 + 28) _
To DateSerial(Year(selDate), Month(selDate) + 1, 1)
weekNum = 6
End Select
WeekOfMonth = weekNum
End Function``````

### Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

#### Joe4

Re: How to Get Monthday from week of month.

I find your question a bit confusing.
It may make more sense if you can provide a few sample dates, along with your expected results and explanation at how you arrived at those results.

#### minmark

##### New Member
Re: How to Get Monthday from week of month.

Hi, Joe.
1,2,3,4,5,....,31,Average, W1(week1),W2,W3,W4,W5
I can't change the table format and need to get week data from daily
I just list the condition of this month, and I find that if I need to use this solution,
I need to list case 1 to 7 .
Is there any better soultion or hint?
Thanks.

Code:
``````Set rng = Cells.Find(what:="W1").Offset(1, 0).Range("A1")
With Application
A = .Weekday(.EoMonth(Now(), -1) + 1)
Select Case A
Case "4"
For Each cell In Range(Cells(rng.Row, rng.Column), Cells(rng.Row + 3, rng.Column))
cell.Value = Application.Average(Range(Cells(cell.Row, cell.Column - 32), Cells(cell.Row, cell.Column - 29)))
Next
For Each cell In Range(Cells(rng.Row, rng.Column + 1), Cells(rng.Row + 3, rng.Column + 1))
cell.Value = Application.Average(Range(Cells(cell.Row, cell.Column - 28), Cells(cell.Row, cell.Column - 22)))
Next
For Each cell In Range(Cells(rng.Row, rng.Column + 2), Cells(rng.Row + 3, rng.Column + 2))
cell.Value = Application.Average(Range(Cells(cell.Row, cell.Column - 21), Cells(cell.Row, cell.Column - 14)))
Next
For Each cell In Range(Cells(rng.Row, rng.Column + 3), Cells(rng.Row + 3, rng.Column + 3))
cell.Value = Application.Average(Range(Cells(cell.Row, cell.Column - 13), Cells(cell.Row, cell.Column - 6)))
Next
For Each cell In Range(Cells(rng.Row, rng.Column + 3), Cells(rng.Row + 3, rng.Column + 3))
cell.Value = Application.Average(Range(Cells(cell.Row, cell.Column - 5), Cells(cell.Row, cell.Column - 3)))
Next
End Select
End With``````

#### Joe4

Re: How to Get Monthday from week of month.

Unfortunately, it is still not really clear. Remember, you are quite familiar with your problem because it is right in front of you and you can see it. But all we have to go on is what (little) you have posted here.
It would be very beneficial if you could post a small snippet of your actual data, and your expected results.
There are tools you can use to post screen images. They are listed in Section B of this link here: http://www.mrexcel.com/forum/board-a...forum-use.html.
Also, there is a Test Here forum on this board that you can use to test out these tools to make sure they are working correctly before using them in your question.

#### Rick Rothstein

##### MrExcel MVP
Re: How to Get Monthday from week of month.

Does this function do what you want? It takes two arguments... the first is any date in the month whose day numbers you want returned to you and the second is the week number whose day numbers you want returned to you. So, for the day numbers in the third week of August, you could write this formula in a cell...

=WeekOfMonth(DATE(2018,8,15),3)

Here is the code for the WeekOfMonth function...
Code:
``````[table="width: 500"]
[tr]
[td]Function WeekOfMonth(selDate As Date, WeekNum As Long) As String
Dim D As Long, FirstSunday As Date, WeekOfMonthSunday As Date
FirstSunday = selDate - Weekday(selDate) + 1
WeekOfMonthSunday = FirstSunday + 7 * (WeekNum - 1)
For D = 0 To 6
If Month(WeekOfMonthSunday + D) = Month(selDate) Then WeekOfMonth = WeekOfMonth & ", " & Day(WeekOfMonthSunday + D)
Next
WeekOfMonth = Mid(WeekOfMonth, 3)
End Function[/td]
[/tr]
[/table]``````

#### minmark

##### New Member
Re: How to Get Monthday from week of month.

Dear Gents,
I finally solve my question...
I use array to store all the condition of the callendar instead of custom function.

the data structure:
1,2,3.....,31,Total, W1(week1),W2,W3,...W6
Daily data sum and average to weekly data.

Thanks.

Code:
``````Set rng = Cells.Find(what:="W1").Offset(1, 0).Range("A1")
With Application
FstMthDay = .Weekday(.EoMonth(Now(), -1) + 1, 15)
B = .Weekday(.EoMonth(Now(), 0))
C = Day(.EoMonth(Now(), 0))
Dim arr As Variant
Select Case FstMthDay
Case "1"
arr = Array(-32, -26, -20, -14, -8, -6, 0)
GoTo Inside1
Case "2"
arr = Array(-32, -27, -21, -15, -9, -6, 0)
GoTo Inside1
Case "3"
arr = Array(-32, -28, -22, -16, -10, -6, 0)
GoTo Inside1
Case "4"
arr = Array(-32, -29, -23, -17, -11, -6, 0)
GoTo Inside1
Case "5"
arr = Array(-32, -30, -24, -18, -12, -6, -7, 0)
GoTo Inside1
Case "6"
arr = Array(-32, -31, -25, -19, -13, -7, -7, 0)
GoTo Inside1
Case "7"
arr = Array(-32, -32, -26, -20, -14, -8, -7, 0)
GoTo Inside1
End Select
GoTo stop2:
Inside1:
For D = 0 To 6
If arr(D + 1) = "0" Then GoTo stop1
For Each cell In Range(Cells(rng.Row, rng.Column + D), Cells(rng.Row + 3, rng.Column + D))
cell.Value = .Average(Range(Cells(cell.Row, cell.Column + arr(D)), Cells(cell.Row, cell.Column + arr(D + 1))))
Next cell
Next D
stop1:
Set rng = rng.Offset(6, 0).Range("A1")
For D = 0 To 6
If arr(D + 1) = "0" Then GoTo stop2
For Each cell In Range(Cells(rng.Row, rng.Column + D), Cells(rng.Row + 138, rng.Column + D))
If Left(cell, 1) = "W" Then GoTo stop3
cell.Value = Application.Sum(Range(Cells(cell.Row, cell.Column + arr(D)), Cells(cell.Row, cell.Column + arr(D + 1))))
stop3:
Next cell
Next D
stop2:
End With``````