Basic Text / Month Question

VoG

Legend

Joined

Jun 19, 2002

Messages

63,650

• Jul 21, 2011

• #2

Try

=TEXT(A1,"mmm")

 

Upvote 0

Jonmo1

MrExcel MVP

Joined

Oct 12, 2006

Messages

44,061

• Jul 21, 2011

• #3

That should be

=TEXT(A1,"MMM")


Hope that helps..

Stay tuned for the "why"...

 

Upvote 0

T. Valko

Well-known Member

Joined

May 9, 2009

Messages

16,623

• Jul 21, 2011

• #4

ekyles said:

Apologies for not challenging with a good VBA question, this is mighty basic.

Trying to convert a month number to three letters is returning "Jan" and not the correct month.

I have a date in Col A : 7/11/11
I have converted to month in Col B using =Text(Month(A1),"MMM")

Every month displays "Jan". Even typing in 7 without the date conversion and using the Text formula returns "Jan". Thanks.

Click to expand...

Remove the MONTH function from your formula:

=TEXT(A1,"mmm")

 

Upvote 0

A

ashtonbt1

New Member

Joined

Aug 25, 2009

Messages

3

• Jul 21, 2011

• #5

Just make the formula =text(A1,"MMM"). The reason it's doing Jan is because the MMM formatting of the date value 7 is 1/7/1900.

 

Upvote 0

Jonmo1

MrExcel MVP

Joined

Oct 12, 2006

Messages

44,061

• Jul 21, 2011

• #6

Why does this not work?
Text(Month(A1),"MMM")


Because Excel stores dates as numbers..
1 = Jan 1 1900
2 = Jan 2 1900
etc..
40745 = July 21 2011

With me so far?

The MONTH function, returns the month NUMBER (1 -12)

So lets break it down
A1: 7/11/11
Text(Month(A1),"MMM")

Month(7/11/11) = 7 (or 11 in UK dates)

So formula becomes

=TEXT(7,"MMM")

7 = Jan 7th 1900

now formula is
=TEXT(1/7/1900,"MMM")
Formula results in January.


Hope that clears it up.

 

Upvote 0

E

ekyles

Board Regular

Joined

Mar 7, 2008

Messages

50

• Jul 21, 2011

• #7

perfect. thanks.

 

Upvote 0