How to get start and end of previous month in VB

Question

Im trying to create some VB code that will get the start and end of the previous month  Im able to the current month which is just   Month DateValue Now     which would return 3  From there I can take away 1 to give me 2 meaning February  This is fine but what about when I Im in January and I repeat this and it gives me zero - my code will fail  Any one know how to get the previous months start and end day then    Thanks

User · Accepted Answer

The first day of the previous month is always 1, to get the last day of the previous month, use 0 with DateSerial:

''Today is 20/03/2013 in dd/mm/yyyy
DateSerial(Year(Date),Month(Date),0) = 28/02/2013 
DateSerial(Year(Date),1,0) = 31/12/2012

You can get the first day from the above like so:

LastDay = DateSerial(Year(Date),Month(Date),0)
FirstDay = LastDay-Day(LastDay)+1

See also: How to caculate last business day of month in VBScript

User · Answer

This works reliably for me in my main sub   Dim defDate1 As Date  defDate2 As Date      Set default date range to previous month defDate1   CDate Month Now   amp    1    amp  Year Now   defDate1   DateAdd  m   -1  defDate1  defDate2   DateAdd  d   -1  DateAdd  m   1  defDate1

User · Answer

Public Shared Function GetFOMPrev ByVal tdate As Date  As Date     Return tdate AddDays - tdate Day - 1   End Function  Public Shared Function GetEOMPrev ByVal tdate As Date  As Date     Return tdate AddDays -tdate Day  End Function   Usage    Get End of Month of Previous Month - Pass today s date EOM   GetEOMPrev Date Today    Get First of Month of Previous Month - Pass date just calculated FOM   GetFOMPrev EOM

User · Answer

Try this  First Day Of Previous Month   New Date Today Year  Today Month  1  AddMonths -1   Last Day Of Previous Month   New Date Today Year  Today Month  1  AddDays -1

User · Answer

firstDay   DateSerial Year DateAdd  m   -1  Now    Month DateAdd  m   -1  Now    1  lastDay   DateAdd  d   -1  DateSerial Year Now   Month Now   1     This is another way to do it  but I think Remou s version looks cleaner

User · Answer

Just to add something to what  Fionnuala Said  The below functions can be used  These even work for leap years    If you pass  2016 20 01  you get  2016 31 01  Public Function GetLastDate tempDate As Date  As Date     GetLastDate   DateSerial Year tempDate   Month tempDate    1  0  End Function   If you pass  2016 20 01  you get 31 Public Function GetLastDay tempDate As Date  As Integer     GetLastDay   Day DateSerial Year tempDate   Month tempDate    1  0   End Function

User · Answer

Try this to get the month in number form   Month DateAdd  m   -3  Now     It will give you 12 for December   So in your case you would use Month DateAdd  m   -1  Now   to just subract one month

User · Answer

I have similar formula for the First and Last Day  The First Day of the month  FirstDay   DateSerial Year Date  Month Date  1    The zero Day of the next month is the Last Day of the month  LastDay   DateSerial Year Date  Month Date   1 0

[ms-access] How to get start and end of previous month in VB

Examples related to ms-access

Examples related to vba