Converting month number to text

[Heather]

Is there a function that converts the month number to text
(ie. 2 to Feb)?

thanks

 

[Dave R.]

With a date in A1,

=TEXT(A1,"mmm")

However if you have only a month number in A1, you can use a lookup or
choose function like this which may be easier than converting it to a fake
date of that month;

=CHOOSE(A1,"Jan","Feb","Mar") <- continue this

then A1=1, "Jan" will be chosen.

 

[hgrove]

Heather wrote...

Is there a function that converts the month number to text
(ie. 2 to Feb)?

=TEXT("2004-"&MonthNumberHere&"-1","mmm"

 

[Heather]

Thanks Dave,
For some strange reason text(A1,"mmm") does not work....it
always returns "Jan", so I used the lookup function and
that was fine...

 

[Don Guillett]

or use this where a1 contains the month number ie 10 = October
=TEXT(DATE(2004,A1,1),"mmmm")

 

[Guest]

Thanks,
this worked!!

-----Original Message-----
Heather wrote...

=TEXT("2004-"&MonthNumberHere&"-1","mmm")

 

[Norman Harker]

Hi Heather!

Or one requiring no functions:

=A1*29
Format mmm or mmmm

 

[Don Guillett]

verrrrrry clever

 

[Norman Harker]

Hi Don!

Thanks! It has limitations because the underlying result is a date in
1900 but we have used the same principle for formatting weekday
numbers as the text day for years (thanks to the stuff up with Leap
Year in 1900).

 

[Terri]

Here are couple of suggestions.

1) Build a master key
Column 1 Column 2
1 January
2 February
3 March

If you build this master key, then you can do a V-lookup
formula (check your functions toolbar)

2) You can do a If/then statement
If(A1=1,"January",if(A1=2,"February,if(A1=3,"March"....

3) Try the "Text to Columns" feature - See Excel Help
for details.

4) Try formatting your cells - it may convert it for you.

Good luck

 

[Harlan Grove]

Or one requiring no functions:

=A1*29
Format mmm or mmmm

For display only. Down stream it'd be a number.

 

[Norman Harker]

Hi Harlan!

Agreed! It's really only a curiosity to be used for a one off
conversion.

You could use =TEXT(A1*29,"mmm") if you really wanted a text form that
would retain text form down stream; but you're back to using a
function.

In any event, I treat it as a curiosity that has an obscure logic that
is likely to confuse.

 

[Myrna Larson]

Evidently you have just a month number, 1-12 in A1, rather than a complete
date.

 

[Myrna Larson]

In testing this, I found the multipliers 28 and 30 also work.

 

[Norman Harker]

Hi Myrna!

Agreed but...

30 only works with the 1900 Date System. Using the 1904 system you get
2*30 = March and 3*30 = March.

But who in their right mind would use the 1904 system? <gdr>