VBA Express : Excel - Week Number Of The Month

Excel

Week Number Of The Month

Ease of Use

Easy

Version tested with

MS Excel 2003, MS Excel 2007

Submitted by:

Mis_Sailesh

Description:

This VBA function can be used for converting date into week number of the month

Discussion:

Use this function in those cases where weekly reporting is required (using dates of the given month), this is very handy in converting the dates into week numbers in vba coding as well

Code:

instructions for use

			Option Explicit 
 
 ' This function was created by Sailesh Kr Mishra (91-9958050139)
 ' while he was required to pull weekly report using dates of the month
 
Function SailWeek(dDate1 As Date) As String 
    Dim dDate2 As String 
    Dim wWeek As Integer 
     
     'dDate2 is changed to date from String
     
    dDate2 = VBA.CDate(Month(dDate1) & "/01/" & Year(dDate1)) 
    wWeek = DateDiff("ww", dDate2, dDate1, vbMonday, vbUseSystem) + 1 
     
     'Return the Week number of the month
     'Monday is taken as week starting date, you can change
     'it to your desired day as starting date of week
     
    SailWeek = "Week " & wWeek 
End Function

How to use:

Open VBE (or Press Alt + F11 from Excel)
Click Insert->Module
Double Clicle on the newly inserted module(located at the left side of the window)
Copy and Paste the entire code
Save the file

Test the code:

Switch (Use Alt + Tab) to Excel
Use the formula (in the cell you want) "=SailWeek(CellAddressOfDate)"
Press Enter

Sample File:

WeekNumOfTheMonth.zip 10.06KB

Approved by Jacob Hilderbrand

This entry has been viewed 258 times.