• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Week number, First day and Last day of the month

Sathish KV

Active Member
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



Code:
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

Excel Ninja
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

Active Member
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

Excel Ninja
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

Active Member
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
 
Top