Week number, First day and Last day of the month

[Sathish KV]

Hi,

The below UDFs (user defined functions) can be used to find week number of the month, first day of the month and the last day of the month with a date

Option Explicit
 
Function FINDWEEK(dDate1 As Date) As String
 
  'Formula Example =FINDWEEK(Date)
 
  Dim dDate2 As String
  Dim wWeek As Integer
 
 
  dDate2 = DateSerial(Year(dDate1), Month(dDate1), 1)
  wWeek = DateDiff("ww", dDate2, dDate1, vbSunday, vbUseSystem) + 1
 
 
  FINDWEEK = "Week " & wWeek
 
 
End Function
 
'---------------------------------------------
 
Function FIRSTDAY(Optional dDate1 As Date = 0) As Date
  'Formula Example =FIRSTDAY(Date)
 
  ' Returns the first day in the specified month.
 
  If dDate1 = 0 Then
 
  ' Did the caller pass in a date? If not, use
  ' the current date.
 
  dDate1 = VBA.Date
 
  End If
 
  FIRSTDAY = DateSerial(Year(dDate1), Month(dDate1), 1)
 
End Function
 
'---------------------------------------
 
Function LASTDAY(Optional dDate1 As Date = 0) As Date
  'Formula Example =LASTDAY(Date)
  ' Returns the last day in the specified month.
 
  If dDate1 = 0 Then
 
  ' Did the caller pass in a date? If not, use
  ' the current date.
 
  dDate1 = VBA.Date
 
  End If
 
  LASTDAY = DateSerial(Year(dDate1), Month(dDate1) + 1, 0)
 
End Function

 

[Deepak]

Hi,

These are standard worksheet function!!!

First Day of the Month = EOMONTH(TODAY(),-1)+1
Last Day of the Month = EOMONTH(TODAY(),0)
Week Number = WEEKNUM(TODAY())

 

[Sathish KV]

Hi Deepak,

These were not availablein excel 2003 and prior

Also the Weeknum function returns the week number of the YEAR while this udf returns the week number of the month

 

[NARAYANK991]

Hi Sathish ,

Just a suggestion - when writing a UDF try to make it as generic as possible , and let it resemble the native Excel functions as far as possible. In your FINDWEEK function , try to eliminate the concatenation within the UDF ; this is something that can be done when this UDF is used in a worksheet cell formula. Also , since the in-built WEEKNUM function returns a numeric value , see if your UDF can also return a numeric value.

All of this ensures that users do not have take too much trouble to remember something additional.

Narayan

 

[Sathish KV]

Hi Narayan,

Thanks your comments!

I have made this UDF for using in my daily activity hence bulit it as per my requirement

Aslo these UDFs can be understood and used only by someone who may know vba, hence it would not be tough for them to change the procedures as per their requirement