Date Format Issue

[jagrenet]

Hello Gurus,

I am using the Date function to return Month and Year only to Cell("A1"). When I run the code it returns "Jan-23" to the cell. What I want it to return is "January 2023". While I recognize that this is a Cell Format issue, I have tried to set the Format through Excel and still cannot get it to return as desired. I feel the formatting needs to be done in the code and have designed it in that manner however, I still can't get it to output the text the way I would like it. (Please see my code below)

Dim MyDate, MyYear
Dim MyMonth As String
Dim MyText As String

MyDate = Date
MyYear = Year(MyDate)
MyMonth = MonthName(Month("01/01/2018"))

MyText = MyMonth & " " & MyYear

Range("A1") = MyText

Debug.Print MyText

The Immediate Window returns perfectly, as expected ...... "January 2023". Cell "A1" continues to return "Jan-23"
Not sure what I am missing here.

Thanks,
Jeff

 

[Jeffrey Mahoney]

Please try this
Range("A1").Value = DateSerial(Year(Now()), Month(Now()), 1)
Range("A1").NumberFormat = "mmmm yyyy"

 

[jagrenet]

Please try this
Range("A1").Value = DateSerial(Year(Now()), Month(Now()), 1)
Range("A1").NumberFormat = "mmmm yyyy"

Brilliant Jeffrey !!!! - Thank you for your time and assistance !