Clarification on use of MONTH and EOMONTH

Perpa

Well-known Member
Joined
Dec 18, 2012
Messages
654
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:
Code:
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'):
Code:
    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:
Code:
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
 
Last edited:

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Use
=UPPER(TEXT(TODAY(),"mmmm"))
Or in code
=Ucase(format(date,"mmmm"))
 
Last edited:
Upvote 0
Code:
myMonth = UCase(Format(Cells(rw1, "J").Value, "mmmm")
 
Upvote 0
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
 
Upvote 0
Glad we could help & thanks for the feedback
 
Upvote 0
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
 
Upvote 0
Code:
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
 
Upvote 0
Solution
I'm glad to help you. Thanks and Happy Easter for you too!
 
Upvote 0

Forum statistics

Threads
1,224,820
Messages
6,181,157
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top