3

I have added text in to my email that goes like:

"please provide numbers for MMMM month end" - where MMMM is the name of the previous month.

So it it's April today, MMMM will show March.

I have the following code:

Dim newDate: newDate = DateAdd("M", -1, Now)

But the result comes out to be 27/03/2017 16:37:58

I want it to show March.

Any suggestions?

wasimakram101
  • 103
  • 1
  • 2
  • 9

3 Answers3

9

Format the return as "MMMM":

Dim newDate: newDate = Format(DateAdd("M", -1, Now), "MMMM")
Scott Craner
  • 148,073
  • 10
  • 49
  • 81
2

If set to True, the month name is abbreviated e.g. Apr

newDate = MonthName(Month(DateAdd("m", -1, Date)), False)
Kostas K.
  • 8,293
  • 2
  • 22
  • 28
0

This will ensure the month is in English irrespective of the regional settings on the computer. This will be helpful for those who are releasing an Excel VBA tool worldwide.

previousmonth = WorksheetFunction.Text(DateAdd("m", -1, Date), "[$-409]mmmm")

For abbreviated months eg: Apr use

previousmonth = WorksheetFunction.Text(DateAdd("m", -1, Date), "[$-409]mmm")
Abilash
  • 21
  • 3