Basic Text / Month Question

[ekyles]

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.

 

[VoG]

Try

=TEXT(A1,"mmm")

 

[Jonmo1]

That should be

=TEXT(A1,"MMM")


Hope that helps..

Stay tuned for the "why"...

 

[T. Valko]

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.

Remove the MONTH function from your formula:

=TEXT(A1,"mmm")

 

[ashtonbt1]

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.

 

[Jonmo1]

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.

 

[ekyles]

perfect. thanks.