Solved: Week Number Of The Month

[Mis_Sailesh]

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
-----------------------------------------------------------------
Copy the above code.
Open any workbook.
Press Alt + F11 to open the Visual Basic Editor (VBE).
From the Menu, choose Insert-Module.
Paste the code into the right-hand code window.
Close the VBE, save the file if desired.
----------------------------------------------------------------
Test the formula in Excel :-

Date Week # Formula
2/24/2010 Week 4 =SailWeek(B2)

:help