VBA Date

[AndrewKent]

Hi there,

If I have a date 20/09/2011, I want to turn this into a string that simply says "September" using VBA.

How can I do this?

Kind regards,

Andy

 

[Joe4]

Try:

Format(DateValue, "mmmm")

 

[VoG]

Try like this

Sub btest()
Dim s As String
s = Format(DateValue("20/09/2011"), "mmmm")
MsgBox s
End Sub

 

[AndrewKent]

Brilliant guys thank you,

And if I wanted to do this via a formula to get the same result, how would I do it? MONTH will give me the numerical value but again I want "September"

Kind regards,

Andy

 

[Joe4]

If you mean directly on a spreadsheet instead of VBA, then, just use "TEXT" instead of "FORMAT", i.e.
=TEXT(A1, "mmmm")

 

[AndrewKent]

Done it:

=TEXT(E8,"mmmm")

Cheers,

Andy

 

[AndrewKent]

Beat me to it Joe!!! thanks!

 

[AndrewKent]

Another VBA one for you. I want to update this VBA routine further, that if the date falls in "September" I want this to show another variable as "October" as well.

 

[Joe4]

Format(DateAdd("m",1,DateValue), "mmmm")

 

[Norie]

You could also try this:

strMonth = MonthName(Month("20/09/2011"))
 
strNextMonth = MonthName(Month("20/09/2011")+1)