How to get current month?

Question

I can't get the current month.

It seems very simple to get the current year and day, as tested with the following:

MsgBox Year(Date)
MsgBox Day(Date)
MsgBox Year(Now)
MsgBox Day(Now)

How is it possible to show the current month as either a number (1, 2 etc.) or a full name?

I could use TODAY() in a cell and convert that in VBA with something like CurrentMonth = MonthName(Month(Sheet1.Range("A1"))) but I would like to do this directly in VBA for Excel.

Answer 1

Try,

debug.print Format(Date, "mmm")       'Mar
debug.print Format(Date, "mmmm")      'March
debug.print Format(Date, "m")    '3
debug.print Format(Date, "mm")   '03

Answer 2

 Month(Now)

Returns the index number associated with the current month.

Jeeped's code below is the most compact, but to give you an idea of how indexes work, the following code will return the month name based on the index returned:

Dim months(11) As String
months(0) = "Jan"
months(1) = "Feb"
months(2) = "Mar"
months(3) = "Apr"
months(4) = "May"
months(5) = "Jun"
months(6) = "Jul"
months(7) = "Aug"
months(8) = "Sep"
months(9) = "Oct"
months(10) = "Nov"
months(11) = "Dec"

Dim nowMonth As Integer
nowMonth = Month(Now)

For i = 0 To 11
  If nowMonth = (i + 1) Then
     MsgBox (months(i))
  End If
Next

Answer 3

Below is how I found the previous month based on the current month name, the assignment to monthNum is the piece needed to solve your question.

month = "February"
'****'
  monthNum = Application.Evaluate("=MONTH(1&" & Chr(34) & month & Chr(34) & ")") 'Returns month #
'****'
If monthNum = 1 Then
    monthNum = 12
Else
    monthNum = monthNum - 1
End If
month = MonthName(monthNum) 'Returns January

Answer 4

Found an easier solution to get the current Month Name

Just use MonthName(Month(Now)) and assign it to a string.

Month(Now) gives you the month number and MonthName() uses that number to display the current month

Answer 5

Here is the best way I have found to do it:

Sub getMonth()

'MsgBox DatePart("m", Date)
'MsgBox Evaluate("MONTH(""" & Date & """)")
'MsgBox VBA.DateTime.Month(Date)

MsgBox Format(Date, "mmmm")

End Sub

Answer 6

A really helpful and simple way is to combine the format function together with date.

Examples (assuming today is Oct 23, 2019):

To get current month as a number as in original question:

MsgBox Format(Date, "mm")

^ Will return: 10

To get current month as short text:

MsgBox Format(Date, "mmm")

^ Will return: Oct

To get current month with full text:

MsgBox Format(Date, "mmmm")

^ Will return: October

You can combine these with days and years as well.

Additional examples:

MsgBox Format(Date, "dd-mmm-yyyy")

^ Will return 23-Oct-2019

MsgBox Format(Date, "dddd-mmmm-dd-yyyy")

^ Will return: Wednesday-October-23-2019

This is creating a custom format, so you can rearrange the dd, mm, yyyy areas as you see fit, such as:

MsgBox Format(Date, "yyyy/mm/dd")

^ Will return: 2019/23/10