CODEWITHSUNDEEP
excelvbadate
Fabsklo
Fabsklo

Reputation: 73

VBA format date to get the previous month

I am fairly new at VBA and this seems like an easy task. I am just trying to get the current date substituting the current month for the previous one and a day constant as 21 so the result will have to be yyyy - (m-1) - 21

so far I had a couple of ideas and they work partially 

Sub Test_Date()
     Dim x As String
     Dim p As String
   p = Format(Date, "mm") - 1
   x = Format(Date, "yyyy-" p "-21")
End Sub

if I MsgBx "p" comesback as what I want but, I dont know the correct syntax to concatenate them into one string

also 

Sub Test_Date()
   Dim x As String
     x = Format(Date, "yyyy-(Format(Date, "mm") - 1)-21")
End Sub

Upvotes: 0

Views: 12901

Answers (4)

Kostas K.
Kostas K.

Reputation: 8518

You could also try this:

Function LastMonth() As Date
    Dim d As Date
        d = DateAdd("m", -1, Date)
    LastMonth = DateSerial(Year(d), Month(d), 21)
End Function

Edit:

Format the returned date as needed:

Sub Test()
    MsgBox Format(LastMonth, "yyyy-mm-dd")
End Sub

Upvotes: 2

Mrig
Mrig

Reputation: 11702

Try

Sub Test_Date()
    Dim d As Date
    d = "22-09-2017"
    d = DateSerial(Year(d), Month(d) - 1, 21)
End Sub

Upvotes: 0

Preston
Preston

Reputation: 8177

Use the dateadd function (https://www.techonthenet.com/excel/formulas/dateadd.php):

DateAdd( interval, number, date )

or

DateAdd("m", 5, "22/11/2003")

Upvotes: 0

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19722

You could use DateSerial.

This accepts a year, month and day as its input and kicks out the date based on that.

So, DateSerial(2017,9,22) will give todays date.
To get the 21st of last month you'd use 

DateSerial(Year(Date), Month(Date) - 1, 21)

Year(Date) returns 2017, Month(Date) returns 9.

Upvotes: 0

Related Questions