Excel number format to convert number of month to name of month

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".

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.

In cell A1 set month number 1.. 12.

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

or

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

=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")

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"

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.