2

I have a cell which will contain a number from 1-12. I would like to set a number format so that this cell will show "Jan"-"Dec" while still containing the numerical value 1-12. I have tried formatting it as "mmmm" but it interprets the number as a date serial number and always returns "January".

6
  • Well I don't really know the problem completely based on what information you provided but you could handle this a couple different ways. The first that comes to mind is looking into custom formatting where you can create your own.
    – Eric F
    Jan 7, 2015 at 20:23
  • @EricF What additional information do you think needs to be provided? I think the question is simple enough.
    – bdr9
    Jan 7, 2015 at 20:25
  • Well why does it need to be automated? I am assuming there is more than just what you said otherwise why not just type the dates? If it is to something bigger then look into what I said with custom formatting.
    – Eric F
    Jan 7, 2015 at 20:27
  • @EricF I see what you mean. The reason is because I have formulas which refer to this cell and expect a number 1-12, but I would still like it to display Jan-Dec. I have looked into custom formatting, but entering "mmmm" just results in "January" as I mentioned in the question. Do you have any ideas regarding a custom format that could accomplish this?
    – bdr9
    Jan 7, 2015 at 20:29
  • would you be open to having a "hidden" cell that contains the number and another that contains the word "Jan"? If the answer is no then custom formatting is really the only option unless you get into VBA. VBA has a monthname function.
    – Eric F
    Jan 7, 2015 at 20:31

7 Answers 7

2

To use any type of format, Excel needs to understand what the number means. There is no basis for Excel to know that 1-12 means Jan-Dec. This means that you need to add functions or some other basis to translate it if you want that cell to display it as a month. Bottom line, there is no way to have a cell contain just the number and display it as a month.

You can use a second cell with the translation, as has been discussed. An alternative is to do it the other way around--store the value in a form Excel can display as a month and then use a function in the formula that references it to translate that back to a number.

2
  • This sums it up pretty well. I guess I'll have to use a helper formula in another cell. Thanks.
    – bdr9
    Jan 7, 2015 at 21:43
  • Excellent answer - on the last point, if A1 contains the month as text, e.g. either Aug or August then you can get the month number (8 in my example) with this formula =MONTH(1&A1) Jan 7, 2015 at 22:03
1

In cell A1 set month number 1.. 12.

=TEXT(EDATE(1;A1)-1;"ММММ")

or

=LEFT(TEXT(EDATE(1;A1)-1;"ММММ");3)
2
  • Thanks, but I would like to be able to do this via a number format directly applied to the cell (applied via Format Cells > Number > Custom).
    – bdr9
    Jan 7, 2015 at 21:03
  • Use MMM but it work at data ... 01.02.2015 - sample)
    – STTR
    Jan 7, 2015 at 21:41
1
=TEXT(A1*29,"mmm")

Excel considers 1 as day 1, 2 as day 2.
By multiplying it by 29 we have a day in each month.

(or)

=CHOOSE(A1,"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")
1

the following number format would do it:

[=1]"Jan";[=2]"Feb";[=3]"Mar";[=4]"Apr";[=5]"May";[=6]"Jun";[=7]"Jul";[=8]"Aug";[=9]"Sept";[=10]"Oct";[=11]"Nov";[=12]"Dec"

2
  • i do not get it Jan 21, 2018 at 20:45
  • I tried this but I got an error saying "Microsoft Excel cannot use the number format you typed."
    – bdr9
    Jan 24, 2018 at 23:56
1

You can use conditional formatting for each number from 1-12

Set conditional formatting to: Cell value equal to 1

Then select "Format", "Number", "Custom", and type in "Jan".

Repeat this process for each number and month.

0

Here is the procedure to doing custom number formatting if the format isn't in the list you want:

  1. Right click on the cell you want to format

  2. Click format cells

  3. Select Custom from the category list

  4. Find the custom format that fits what you want the closest.
    In your example I selected mmm-yy

  5. While having a type selected you can modify it. Edit the text box under type to the format you want. In your case change it to mmm. Select Ok and now you can select this format for the rest

A list of formats that can be used this way are found here

4
  • Thanks for this, but it doesn't really answer my question. I already know how to use custom formatting. My question is what custom format can I use to accomplish this. None of the formats in the built-in list or the list in the link you posted can directly convert a month represented as 1-12 to Jan-Dec.
    – bdr9
    Jan 7, 2015 at 20:50
  • mmm is the format you want though?
    – Eric F
    Jan 7, 2015 at 20:52
  • No, mmm doesn't accomplish what I am trying to do because it interprets the cell value as a date serial number, and returns Jan for all numbers 1-12 (Serial number 1 corresponding to date 1/1/1900 and 2 to 1/2/1900, etc.)
    – bdr9
    Jan 7, 2015 at 21:00
  • Ah sorry I see the problem. Possibly you could do like I showed here but change it so your entry is 1/1/1900, 2/1/1990, 3/1/1990.. etc formatted as mmm then in the reference formulas, reference it as month(cell) instead of just cell since month(cell) will return a number. I think the only way to achieve your result is to change how the resulting formulas grab the value.
    – Eric F
    Jan 7, 2015 at 21:08
0

I had the same issue. The solution is to use the below formula. If cell A1 contains the number 1 to 12, you can map it to the months of the year all by yourself.

=CHOOSE(A1,"JAN","FEB","MAR","APR",......."DEC")

1
  • "I would like to set a number format so that this cell will show "Jan"-"Dec" while still containing the numerical value 1-12." This solution doesn't do that.
    – fixer1234
    May 5, 2018 at 7:27

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .