MrExcel Publishing
Your One Stop for Excel Tips & Solutions

first day of month&las day of month


Posted by J. Harms on January 28, 2002 1:49 AM

How can i find the first day and last day of current month using vba?


Posted by Ivan F Moala on January 28, 2002 4:17 AM

Try this VBA routine...change as required

Sub LastandFirstDayOfCurrentMonth()
Dim dYr As Double, iMth As Integer
Dim sMth As String
Dim LastDay, LastDayDate, FirstDay, FirstDayDate
Dim sMsg As String

dYr = Year(Now)
iMth = Month(Now) + 1
sMth = Format(Now, "mmmm")
'Get last day info
LastDayDate = Format(DateSerial(dYr, iMth, 0), " dd/mm/yy")
LastDay = Format(DateSerial(dYr, iMth, 0), "dddd")
MsgBox "The last day of the current month of " & sMth & " is " & LastDay & LastDayDate

'get 1st day info
FirstDayDate = Format(DateSerial(dYr, iMth - 1, 1), " dd/mm/yy")
FirstDay = Format(DateSerial(dYr, iMth - 1, 1), "dddd")
MsgBox "The 1st day of the current month of " & sMth & " is " & FirstDay & FirstDayDate

End Sub

HTH


Ivan