Use below formula to generate named 3 lettered month like Jan, Feb….Dec
=TEXT(A1*30,"mmm")
Replace "mmm" with "mmmm" to generate full name of the month like January, February….December in any of the formulas in this post.
Other alternative formulas to achieve the same thing
=TEXT(DATE(0,A1,1),"mmm") [ in fact, you can just write =TEXT(DATE(,A1,1),"mmm") OR =TEXT(DATE(1,A1,1),"mmm") ]
=TEXT(EDATE(0,A1),"mmm")
=TEXT(EOMONTH(0,A1)-31,"mmm") – This formula has lesser value as above other formulas are sleeker. But I am using this to illustrate the concept.
Explanation of the above formulas
Excel stores the dates as numbers. Excel has no date format. The lowest date which Excel can store is 1-Jan-1900 which has a number 1. Basically, Excel determines the number of days between 0-Jan-1900 and a date and that number is used to refer to date. Hence, if you type 29-Aug-15 and format the cell as number format, you will notice 42245. Hence, Excel uses 42245 to represent the date 29-Aug-15. Now, how this 42245 is arrived at. Put 1-Jan-1900 in A1 and 29-Aug-15 in A2 and put following formula
=A2-A1+1 and you will get 42245. (basically, it should be 29-Aug-15 – 0-Jan-1900 but since if you type 0-Jan-1900, Excel won't recognize this as a date. But if you do, 1-Jan-1900 – 1, it will generate an answer of 0-Jan-1900 and this formula generated date is recognized a valid date)
Now Excel uses this serial number of the date to determine everything related to dates. Hence, if you put 59 for example, it knows that it is talking about a date in Feb month as it will just simply do 0-Jan-1900 + 59 = 28-Feb-1900 (1-Jan-1900 – 1 + 59). You need to put 1-Jan-1900 in a cell let says A1, and in another cell you can put A1-1+59 to generate this value. If you want to use this formula as it is, then you will have to enclose date in double quote like "1-Jan-1900"-1+59 and format this as date. Recommendation is to put dates in cell and apply formulas referencing to those cells.
Now, if you put a formula =MONTH("28-Feb-1900"), you will get the answer of 2 which is Feb month. Hence, the trick is to get a value as day which falls in that month and Excel will get the month of that particular month.
Now let's put a value of 120. So, you need to put 1-Jan-1900-1+120 = 29-Apr-1900. Hence, if you use MONTH formula on this date, you get a month value of 4 which is April.
Below are the date serial numbers and the operation of TEXT formulas on those. (You need not use even TEXT formula and you can simply put =A1*30 formula and custom format this cell as "mmm" and it will display corresponding month. The same will be true for all other succeeding formulas)
Note – Some of the formulas also exploit the fact that 29-Feb-1900 is a valid date in Excel i.e. year 1900 is a leap year. In fact, this is not the case i.e. year 1900 is not a valid date but Excel has chosen to live with this.
Refer – Excel 2000 incorrectly assumes that the year 1900 is a leap year
Formula – =TEXT(A1*30,"mmm")
Formula – =TEXT(DATE(0,A1,1),"mmm")
Formula – =TEXT(DATE(1,A1,1),"mmm")
Formula – =TEXT(EDATE(0,A1),"mmm")
Formula – =TEXT(EOMONTH(0,A1)-31,"mmm")
Note – The below tips & tricks is for US date format. For other date formats, the formula will have to be changed appropriately. Whereas the above formulas are independent of the date format. Hence, this I am talking about just as an academic curiosity and I recommend not to use this as this is dependent upon Excel's date format.
Suppose you have a number in cell A1 say 3 in this case, you want to display "Mar" for this number. Similarly, Jan for 1, Feb for 2……Dec for 12.
Use below formula
=TEXT(A1&"/1","mmm")
If you want to show full month's name like January, February
=TEXT(A1&"/1","mmmm")
For DMY format, you can change the formula to
=TEXT("1\"&A1,"mmm")
yes