VBA Code for converting a date into a month for use in Pivot Tables

[spraff]

I currently use the following VBA module code to convert dates to a Week Number:

Function VBWeekNum(dt As Date, WkStart As Long) As Long​

VBWeekNum = DatePart("ww", dt, WkStart)​

End Function​

And use the forumula to populate the cell (our week goes Fri-Thurs)

=VBWeekNum([@[Date PST]],6)

​

I'd like to use something similar for converting dates to month names for use in Pivot Tables (am I missing something, as it seems there should be an inherent way to do this w/in Excel - currently I'm just manually adjusting the months so my Slicer has ONE button for each month rather than one per individual date/time).

I'm a complete novice in VBA. I've tried hacking away at code provided from two other sites to no avail.

Any help greatly appreciated!

​

 

[V_Malkoti]

VBA has a function that returns name of the month number provided to it:

MonthName(Month(Date), False)

Documentation here: https://msdn.microsoft.com/en-us/library/zxbsw165(v=vs.90).aspx

 

[spraff]

Thank you V_Malkoti

But, I'm still a noob!

So, writing the module with this doesn't suffice:

Function MonthName( _​

ByVal Month As Integer, _​

Optional ByVal Abbreviate As Boolean = False _​

) As String​

​

End Function​

Nor does:

Function MonthName()​

​

Dim thisMonth As Integer​

Dim name As String​

thisMonth = 4​

' Set Abbreviate to True to return an abbreviated name.​

name = MonthName(thisMonth, True)​

' name now contains "Apr".​

​

End Function​

What do I need in the Module box? And what formula would I need? I'm using this for a worksheet that has 114,000+ unique date/time stamps (one per row) in the Time PST column.

Many thanks!

 

[V_Malkoti]

Create a UDF. Paste following code in a module:

Function GetMonthName(dt As Date, abbr As Boolean) As String
    GetMonthName = MonthName(Month(dt), abbr)
End Function

you can then use it in spreadsheet cells as:

=GetMonthName(B1, False)

 

[spraff]

Awesome! Thank you V! Much appreciated!

It didn't work in the spreadsheet I wanted it to work in, but did in another. Will try to hunt down why it's not working in the desired xls. Again, thank you!