Converting Month Number to Month Text Abbreviation

B

Bob

Example:
Cell A1 = 11/17/2007
Using a function/formula, I would like cell B1 to display "Nov" (without
using any date formats) . . . as if I had simply typed "Nov" (without the
double quotes) in cell B1.
I would greatly appreciate any help.
Thanks,
Bob
 
G

Gary''s Student

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

Using date formats is a lot easier.
 
A

Andy

Hi there,

I used the function but the result show "Jan" only, please help!!!

A1 : 15/4/07
B1 : =IF(ISBLANK(A1),"",MONTH(A1)) 'show 4 in the cell
C1 : =Text(IF(ISBLANK(A1),"",MONTH(A1)),"mmm") 'show Jan in the cell
 
T

T. Valko

Same result to show Jan only, why????

That doesn't make any sense!

I use the U.S. date format mm/dd/yyyy

A1 = 4/15/2007

=IF(ISNUMBER(A1),TEXT(A1,"mmm"),"")

Correctly returns: Apr

If A1 is either *empty* or contains TEXT the formula will return a blank "".

If it's returning Jan then there's something "funny" about the date.

If the cell contains a numeric 0 but you suppressed 0 display this will
return Jan.

Format cell A1 as GENERAL. When you do that the value should be 39187. If it
isn't then your date of 15/4/07 is not a true Excel date but then the
formula should return a blank. So, there's something you're not telling me!

Biff
 
D

Dave Peterson

Try formatting A1 as an unambiguous date.

Give it a custom format of:
mmmm dd, yyyy

What do you see?
 
Top