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

Toney

Answer 1

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

Sisco

Hi Remou was just going over your code again and have another question - why is it that when i do this: LastDay = DateSerial(Year(Date), Month(Date) - 1, 1 - 1) i get 31/01/2013 instead of 28/02/2013? Surely the Month(Date) - 1 returns 2 implying February – Toney

You have month -2, once for month(date)-1, once for 0 for day. The zeroth day of this month is the last day of the previous month, so February is dateserial(2013,3,0), not as you show. – Sisco

ohh ok - thats decieving. Thanks – Toney

?DateSerial(Year(12/1/2015),Month(12/31/2015),0) 11/30/1899 <--- this is what I get when I run this in VB immediate window. How can I make it so it shows 2015 instead of 1899 – Autostability

@Autostability You are passing 12/1/2015 which gets interpreted as math and evaluates to somethings like this: DateSerial(Year(0.00595533498759305210918114143921),Month(0.00019210758024493716581229488513568),0) Enclose the dates in quotes like this: DateSerial(Year("12/1/2015"),Month("12/31/2015"),0) . Excel won't implicitly convert ints to dates but it will convert strings to dates. – Gotthelf

Answer 2

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) 

Augean

Answer 3

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.

Embarkation

Answer 4

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)

Anticlastic

This is not VBA. – Ralph

Answer 5

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) & "/1/" & Year(Now))
defDate1 = DateAdd("m", -1, defDate1)
defDate2 = DateAdd("d", -1, DateAdd("m", 1, defDate1))

Chaille

Answer 6

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.

Scorch

How does that get both start and end day of previous month? – Pothunter

Answer 7

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

Repeater

Answer 8

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)

Hove

Pretty sure that's not VBA. – Epiphany

Why not adding an explanation to your code to improve the post? – Breskin

I haven't used VBA in several years - can someone tell me if this is a better solution to the accepted answer? Though I expect an upvote would answer my own question... – Toney