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".
Excel number format to convert number of month to name of month
microsoft excel
Related Solutions
Format it as number with 11 digits:
Alternatively, you can also provide a custom format, such as #,##0.00000000000
Subtraction of dates in Excel returns the difference between the dates in days. Use the following to find the day # in the year for a date in A1:
=VALUE(A1-DATEVALUE("Jan 1")+1)
To find the number of days left in the year, use the following related formula:
=VALUE(DATEVALUE("Dec 31")-A1)
The VALUE functions are used to avoid having to apply a number format to the cell. If not used, Excel will format the result as a date.
Best Answer
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.