Clarification on use of MONTH and EOMONTH

[Perpa]

Greetings everyone! I have a question which I am sure can be resolved by this forum:


Using the formulae shown, I get the results shown to the left thereof:

4    =MONTH(TODAY())
January    =TEXT(MONTH(TODAY()),"mmmm")
JANUARY    =Upper(TEXT(MONTH(TODAY()),"mmmm"))

It is unclear to me why the month goes to 'January' and 'JANUARY' in the second 2 formulae, especially
when the first formula shows '4' which is of course 'April'. Perhaps someone might explain.


I needed the month to be uppercase to check for a match to the current calendar month, so I resorted to the brute
force method which works fine (Note the 'Cells(rw1, "J")' refers to a list of dates in the format '4/10/2019'):

    MonthNum = Month(Cells(rw1, "J"))     'Gives the number of the month, ie. 1, 2 to 12
    If MonthNum = 1 Then myMonth = "JANUARY"
    If MonthNum = 2 Then myMonth = "FEBRUARY"
    If MonthNum = 3 Then myMonth = "MARCH"
    If MonthNum = 4 Then myMonth = "APRIL"
    If MonthNum = 5 Then myMonth = "MAY"
    If MonthNum = 6 Then myMonth = "JUNE"
    If MonthNum = 7 Then myMonth = "JULY"
    If MonthNum = 8 Then myMonth = "AUGUST"
    If MonthNum = 9 Then myMonth = "SEPTEMBER"
    If MonthNum = 10 Then myMonth = "OCTOBER"
    If MonthNum = 11 Then myMonth = "NOVEMBER"
    If MonthNum = 12 Then myMonth = "DECEMBER"

Recently I created a single line of code that provides the desired results as well:

myMonth = UCase(Application.Text(Application.EoMonth(Cells(rw1, "J"), 0), "mmmm"))

It baffles me why the 'EOMONTH' function does what is needed, but the 'MONTH' function does not.
I would appreciate some clarification if possible.
Many thanks.
Perpa

 

[Fluff]

Use
=UPPER(TEXT(TODAY(),"mmmm"))
Or in code
=Ucase(format(date,"mmmm"))

 

[shg]

myMonth = UCase(Format(Cells(rw1, "J").Value, "mmmm")

 

[Perpa]

Fluff and shg
Obviously I was making this much more difficult than it needed to be...not the first time (forrest-trees)!
Thank you both.
Perpa

 

[Fluff]

Glad we could help & thanks for the feedback

 

[Perpa]

shg,
I just tried your formula 'myMonth = UCase(Format(Cells(rw1, "J").Value, "mmmm")'
It did not work...I got this error: 'Compile Error >>Syntax Error'


my dates are entered as '5/10/2019' and the cells are formatted as 'Dates' and '*5/10/2019'


Not sure why I got the error, but I thought you should know. Maybe something to do with my computer, Windows 7, Excel 2007.


I'm going back to my 'EoMonth' formula which does work for me... Sometimes it's easier not to fight it if it works.
Thanks again for the effort.
Perpa

 

[DanteAmor]

I just tried your formula 'myMonth = UCase(Format(Cells(rw1, "J").Value, "mmmm"))'

missing a parenthesis

 

[DanteAmor]

4    =MONTH(TODAY())
January    [COLOR=#ff0000]=TEXT(MONTH(TODAY()),"mmmm")[/COLOR]
JANUARY    =Upper(TEXT(MONTH(TODAY()),"mmmm"))

It is unclear to me why the month goes to 'January' and 'JANUARY' in the second 2 formulae, especially
when the first formula shows '4' which is of course 'April'. Perhaps someone might explain.

Answering your initial question


=TEXT(MONTH (TODAY ()), "mmmm")


=TEXT(MONTH (04/21/2019), "mmmm")
=TEXT(4, "mmmm")
=TEXT(4 January 1900, "mmmm")


Excel converts the 4 to the 4 of January 1900, that is, the 4th day of the excel record, the 1st day is 1 January 190, the 4th day is 4 January 1900, the 4 day of the year 1900, because the format you suggest "mmmm" is dated .
So
= TEXT (January)
= January

 

[Perpa]

missing a parenthesis

DA...Thank you so much! That parenthesis took care of it...Yea!
Happy Easter to all!
Perpa

 

[DanteAmor]

I'm glad to help you. Thanks and Happy Easter for you too!