VBA Macro and changing date format to mmm-yy

1
votes

I am having problems with an excel macro (VBA) that is meant to grab a date from an excel spreadsheet, subtract one month and reformat it to MMM-YY. Basically I want to take 3/31/2013 and convert it to Feb-13

Here is my code:

Dim ReportDate As Date
ReportDate = Worksheets("Current").Cells(2, 16) 'ex. 03-31-2013
prevMonth = Format((Month(ReportDate) - 1) & "/" & Day(ReportDate) & "/" & Year(ReportDate), "mmm") & "-" & Format(ReportDate, "yy")
Debug.Print prevMonth

The result I get is 2/31/2013-13

So I tried changing the prevMonth variable:

prevMonth = Format((Month(ReportDate) - 1) & "/" & Day(ReportDate) & "/" & Year(ReportDate), "mmm-yy")

But got just 2/31/2013 again

I tried to declare prevMonth as an Integer or a Date but I get a type mismatch error. I can only declare it as a String but it still doesn't help the program.

Thanks in advance for your help.

1
vbaexcel

1 Answers

6
votes

Try this

Sub Demo()
    Dim ReportDate As Date
    Dim prevMonth As Date

    ReportDate = Worksheets("Current").Cells(2, 16) 'ex. 03-31-2013
    prevMonth = DateAdd("m", -1, ReportDate)
    Debug.Print Format(prevMonth, "mmm-yy")
End Sub