EXCEL: How can you FORMAT a serial number into a MONTH ( Jan, Feb)

Question

I extract the Month() number out of my date. I now want to change that number into a recognisable string Jan, Feb, Mar, etc. In Access you can use FORMAT, but what can we use in Excel?

E.g.

Date----------Formula---------Result-----What I want!
01/03/2010---Month(Date)--------3----------MAR

Thanks Mike

Solution 1

You can Format Cells (right click on cell you want to format) -> Category:Custom and type:

mmm

this is short month format

EDIT:

what I wrote would work on full date not on a single number, so if you do not need to go through all the steps (especially the =MONTH formula) this could be the answer

Solution 2

You can format your date (in cell A1) to a text string using the formula:

=TEXT(A1, "mmm")

or you can leave it as a number but format it as a month:

Format Cells
  Category: Custom
    mmm

The month formats you can use are:

mmmm - Full month name
mmm - Abbreviated month name
mm - numeric month, zero padded
m - numeric month

Comments

[Sux2Lose]

I think this is the correct answer. If you need the date and month next to each other, instead of =month(datecell) just make it =datecell and change the custom formatting as described in this answer.

[Sux2Lose]

The problem I see with this answer is that sorting by the month column will be alpha based instead of numerical. Therefore April will come before March, etc.

[RocketGoal]

I sued the simple =Datecell and then formatted "mmm" in custom formats. So simple I forgot to do it. It's had the added benefit of organising the dates as well when I add mmm-yy to the format! Bonus.

[RocketGoal]

Comprehensive answer. Thank you for your time. I used the solution below, but yours is one that had helped me use TEXT (which I will come back to I'm sure)