CODEWITHSUNDEEP
excelweek-numbervba
Mohammed Zegui
Mohammed Zegui

Reputation: 83

Excel VBA : week numbers

I'm using Excel VBA, How can I ?

Upvotes: 0

Views: 6365

Answers (1)

danieltakeshi
danieltakeshi

Reputation: 939

To get week from Date

Input a date and get the week number for this date.

Code

Sub WeekDate()
    Dim InputDate As Date
    Dim week As Long

    InputDate = Now 'Change to desired date/cell
    week = Application.WorksheetFunction.WeekNum(InputDate)
    Debug.Print week
End Sub

Result

Result1

To get all weeks on month

Input month and year and return all week numbers for this month.

Code

Sub WeeksInMonth()
    Dim MonthYear As String, txt As String
    Dim InputDate As Date, MonthYearDay As Date
    Dim i As Long, intDaysInMonth As Long, j As Long
    Dim MyArray As Variant
    Dim arr As New Collection, a
    ReDim MyArray(0 To 31)
    j = 0
    InputDate = Now
    MonthYear = Month(InputDate) & "/" & Year(InputDate)
    intDaysInMonth = Day(DateSerial(Year(MonthYear), Month(MonthYear) + 1, 0))
    For i = 1 To intDaysInMonth
        MonthYearDay = DateSerial(Year(InputDate), Month(InputDate), i)
        MyArray(j) = Application.WorksheetFunction.WeekNum(MonthYearDay)
        j = j + 1
    Next i

    ReDim Preserve MyArray(0 To j - 1)
    On Error Resume Next
    For Each a In MyArray
        arr.Add a, CStr(a)
    Next

    For i = 1 To arr.Count
        Debug.Print arr(i)
    Next
End Sub

Result

Result2

Explanation

  • The code first get all the days on a month with intDaysInMonth, so on February of 2017 it will output 28.
  • Loop on each day of the month with MonthYearDay and populate the array MyArray with the week of that day Application.WorksheetFunction.WeekNum(MonthYearDay).
  • Then create the collection arr(i) to output the unique values from the array MyArray

On future requests, post what you have tried. I am answering it because i couldn't find a suitable answer for: Input month and year and return all week numbers for this month.

Upvotes: 2

Related Questions