Converting number(1..12) to Month (MMM) 3

[Stretchwickster]

Hi people,

Apologies if this is a completely obvious question, but I've had a good look in the Excel help and online and can't seem to find what I'm looking for. How would I convert a number between 1 and 12 into a 3-letter month e.g. 12 becomes DEC.

Your help would be much appreciated!

Clive

 

[TonyJollans]

Hi Clive,

In a cell in Excel, use ..

[blue][tt]=TEXT(Number,"ddd")[/tt][/blue]

Come back if you want it in Word.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[Stretchwickster]

Thanks for your reply Tony. Yes, it was for Excel, but it generates days rather than months!

I want: 1 returns Jan
2 returns Feb etc.

Clive

 

[frainbreeze]

try using "mmm" instead of "ddd"

 

[Stretchwickster]

I tried that but it seems to always return "Jan". The cell formula I am using is:

=TEXT(RANDBETWEEN(1,12),"mmm")

Clive

 

[TonyJollans]

Hi Clive,

Sorry about the ddd.

Fascinating - the Randbetween result appears to be being completely ignored. I will investigate and report back.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[xlbo]

Tony - ??????

Clive - Formatting a 1 to mmm will ALWAYS return Jan as 1 is the serial number of 1/1/1900 (which is in Jan). Because your randbetween function only returns 1-12, this will also ALWAYS return Jan as 1-12 refers (datewise) to days 1-12 of Jan in the year 1900

If your number is in A1, then

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

will work

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[Rigger123]

This may not be what you want, but you could do this using a VLOOKUP. i.e.

=VLOOKUP(A1,F1:G12,2)

A1 is where you type the number (1-12)
F1:G12 is a table with 1-12 and the correct month typed next to each number respectively.
the 2 is what coloumn the month is typed in.



He who smiles in a crisis has found someone to blame.

 

[TonyJollans]

Hi Clive,

Complete brain freeze, obviously.

The text function is treating the number (random or otherwise) as days, not as months. The easiest way is to try using ..

[blue][tt]=TEXT(RANDBETWEEN(1,366),"mmm")[/tt][/blue]

Note the 366. This is because the numbers represent a number of days in the year 1900, which Excel deliberately treats as a leap year.

The results will be proportionate to the lengths of the months (in a leap year), so February will occur slightly less than the others, etc.



Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[Stretchwickster]

Thanks everyone for your very prompt replies, Tony's (2nd!) suggestion works in exactly the way I was looking for. Sorry xlbo, didn't give yours or Rigger's a go. Was looking for a fast fix.

Clive

 

[xlbo]

I'm sorry Clive but how does that answer the original question ??

How would I convert a number between 1 and 12 into a 3-letter month

If you enter any number between 1 and 12 into that formula, you will get Jan EVERY TIME. The formula can work but to get Feb, you will need to enter a number between 32 & 60, March would be any number between 61 and 91 etc etc

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[KenWright]

With number from 1-12 in A1, in any other cell, formatted as Date / Custom / "mmm"

=DATE(1,A1,1)

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[TonyJollans]

Hi Geoff,

It's a bit embarrassing to get a star when I got it wrong.

I guess Clive didn't actually have a number between 1 and 12 to start with which was why changing the input to the TEXT function gave him what he wanted (which wasn't clear from the original question), a random month of the year.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[xlbo]

'sok Tony - I've had it myself. Just wanted Clive to be clear

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[Stretchwickster]

Sorry for the confusion everyone! I should have been a bit clearer at the outset. Geoff, you are right - I did end up drifting from my original question - I just wanted something quick that would work as a random month generator. I later tried your code and it does match my original request plus gives an even spread (including Feb) so thank you.

My code ended up looking like this:

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

Clive

 

[Bowers74]

Instead of using the "List" for the months, I would suggest using the combination of all of the suggestions in this post:

=TEXT(DATE(1900,RANDBETWEEN(1,12),1),"mmm")

Will also do the same thing, but with, IMHO, better syntax. Plus, if you ever want to change the month values to the full month name, you just need to use Excel's Find/Replace Tool as follows:

Replace "mmm" with [COLOR=green yellow]"mmmm"[/color] instead of

Replace "Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec" with [COLOR=red yellow]"January","February","March","April","May","June","July","August","September","October","November","December"[/color]

Peace!!

Mike

Didn't get the answers that you wanted? Take a look at FAQ219-2884

 

[KenWright]

=DATE(1,RANDBETWEEN(1,12),1)

Formatted as Date / Custom / mmm

Regards
Ken..............


----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------