Get Monthday from week of month.

[minmark]

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.

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

 

[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]

Re: How to Get Monthday from week of month.

Hi, Joe.
Table header as following:
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.

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]

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

[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]

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.

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