extract the month of a date

G

gireesh

I want to extract the month of a date. for example I have dat
"6/5/2005" which is in "mm/dd/yyyy" format. i want to get the mont
part of the date as "Jun". Similarly for the date "8/12/2005" a
"Aug".

Presently I am doing this with the help of VLOOKUP approach with
table having these values.

As i am having a huge volume of data to work with a quicker and easie
formula can be a great help.

Thank you
 
P

Paul B

giressh, with the date in A1, in another cell put =A1 and format the cell as
mmm to show only the month

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003
 
B

Bryan Hessey

Rightmouse and Format Cell to Custom format = mmm

then =Month(A1)

shoud give you your required answer.
 
J

JMay

I've entered all months of the year,
from 01/15/05,,,,,, to 12/15/05
in Cell A1 (one at a time of course)
Each time my cell B1 =month(A1)
yeilds 1,,,,,,,12 << Great (as expected).
But when I format B1 as Custom "mmm"
they come out Jan,,,,,,,,,Jan
What's my problem?
TIA,

"Bryan Hessey" <[email protected]>
wrote in message
 
R

Roger Govier

Hi

It sounds as though you have not put =A1 into cell B1
If the cell is empty, Excel assumes 01/01/1900 if a date format or function
hence it would keep returning Jan if empty.

Regards

Roger Govier
 
B

Bryan Hessey

Yes, it only worked for Jan.

Better is

=LOOKUP(MONTH(A1),{1,2,3,4,5,6,7,8,9,10,11,12},{"jan","feb","mar","apr","may","jun","jul","aug","sep","oct","nov","dec"})

which accomodates the other eleven months also.
 
J

JMay

I am continually amazed at how this product
seems to have such a way of proving my stupidity!
Of course, if in cell B1 I have =month(A1), say 10,
then 10 is the "raw" value in B1 and the 10th day from
01/01/1900 is Jan 11, 1900;
Crap -- I hope I never forget this incident...
Thanks Roger!
Jim
 
R

Roger Govier

Hi Bryan

Formatting a cell as mmm will display the Month name Jan, Feb etc. dependent
upon the serial date number held within that cell. It does not require the
use on the MONTH() function, which returns a numeric 1 to 12.

Alternatively rather than a long Lookup, you can use =Text(A1,"mmm") to
return Jan, Feb, etc.

Regards

Roger Govier
 
B

Bryan Hessey

Unfortunately Jim you are not alone . . .

I am continually amazed at how this product
seems to have such a way of proving my stupidity!
Of course, if in cell B1 I have =month(A1), say 10,
then 10 is the "raw" value in B1 and the 10th day from
01/01/1900 is Jan 11, 1900;
Crap -- I hope I never forget this incident...
Thanks Roger!
Jim
Click to expand...
 
R

Roger Govier

Hi Jim

You have to be clever to be stupid!!
At least, that's what I tell myself when I frequently do similar type
things<bg>.

Regards

Roger Govier
 
J

JMay

Thanks guys,
Hope you have a great TG day.


Roger Govier said:
Hi Jim

You have to be clever to be stupid!!
At least, that's what I tell myself when I frequently do similar type
things<bg>.

Regards

Roger Govier
Click to expand...
 
B

Bryan Hessey

Hi Roger,

Yes, I know that the Month() of a May date will give =5, which
displayed as "mmm" will give Jan, but that didn't stop my first post.
However I had forgotten the Text option when I tried to beak the 'mmm'
free of a date format in the Lookup, thanks for that, and lets hope
that gireesh gains something from this too.


Roger said:
Hi Bryan

Formatting a cell as mmm will display the Month name Jan, Feb etc.
dependent
upon the serial date number held within that cell. It does not require
the
use on the MONTH() function, which returns a numeric 1 to 12.

Alternatively rather than a long Lookup, you can use =Text(A1,"mmm")
to
return Jan, Feb, etc.

Regards

Roger Govier


Click to expand...
 
Top