I like to display the name of the month when I want to create some automatic comments, of course, there are many whys we want the name, the answer will depend on what it is more convenient and for which type of reports.

 

When I use the formula ?

To create comments that will update automatically the months.

 

How to use the formula ?

The formula in this topic is with "," so depending of the operating system of your PC, the formula should have ";" instead of ",".

 

How are the formulas ?

=TEXT()

=DATE()

=YEAR()

=MONTH()

=NOW()

=TODAY()

=EOMONTH()

=VLOOKUP()

Normally, I have a cell with the date, in cell A2 I have 14/03/2019. There are 2 ways to display the name:

• 1. Using the “format cells” option (right click in the cell) by selecting “custom” and put in “type”: mmmm
• 2. Using this formula in cell B2 =TEXT(A2,"mmmm")

If I just want the first 3 letters of the month, I put 3 ms (mmm) instead of 4 ms (mmmm).

I can combine with vlookup to search for a word:

=TEXT(VLOOKUP("chocolate",A:C,2,0),"mmmm")

First this formula will find “chocolate” in the column A. Once it finds it, on the column B (2) where is the date number (for instance 18/06/2019), it will show the corresponding month, in this case “June”.

Now, if I have a number from 1 to 12, in the column A (picture below), and I put the above formula =TEXT(A2,"mmmm"), I will get “January” for all numbers, this is because excel interprets those numbers as a day of January (column B).

In such situation, I have to put the day average of a full year which is 30 (in fact, it is 30.41) so the formula is =TEXT(A2*30,"mmmm"). In fact, I can put 28, 29 or 30 but not smaller or bigger, for instance 27 or 31, excel will not display the correct name.

In some situations, I don’t have a date or a number but I need to have the month, in this case, I will use the date mixing with the text or the eomonth function. The difference is that the date function is to have any day I want but usually, it is to get the first day of the month and the eomonth, it is only to get the last day of the month.

The date mixing the text functions:

=TEXT(DATE(YEAR(NOW()),MONTH(NOW()),1),"mmmm")

It will give me the current month and the number 1 is to say the day, if I want the day 15, I will put 15 instead of 1. If I want the previous month, I just change MONTH(NOW()) to MONTH(NOW())-1 and for the next month MONTH(NOW())+1 so it will be like that:

=TEXT(DATE(YEAR(NOW()),MONTH(NOW())-1,1),"mmmm")

=TEXT(DATE(YEAR(NOW()),MONTH(NOW())+1,1),"mmmm")

The eomonth function:

=EOMONTH(NOW(),0)

It will give me the current month, if I want the previous month, I just change 0 to -1 and for the next month 1 so it will be like that:

=EOMONTH(NOW(),-1)

=EOMONTH(TODAY(),1)

Just one thing more for the eomonth function, I need to format the cell with the “custom” option to display the name of the month.