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.
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
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
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
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