extract the month of a date
 

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

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

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

Thank you.


--
gireesh
------------------------------------------------------------------------
gireesh's Profile: http://www.excelforum.com/member.php...o&userid=29027
View this thread: http://www.excelforum.com/showthread...hreadid=487578

extract the month of a date
 

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

"gireesh" wrote in
message ...

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

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

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

Thank you.


--
gireesh
------------------------------------------------------------------------
gireesh's Profile:
http://www.excelforum.com/member.php...o&userid=29027
View this thread: http://www.excelforum.com/showthread...hreadid=487578

extract the month of a date
 

Rightmouse and Format Cell to Custom format = mmm

then =Month(A1)

shoud give you your required answer.



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

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

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

Thank you.


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=487578

extract the month of a date
 

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"
wrote in message
news:Bryan.Hessey.1yy79a_1132746601.0002@excelforu m-nospam.com...

Rightmouse and Format Cell to Custom format = mmm

then =Month(A1)

shoud give you your required answer.



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

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

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

Thank you.


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile:
http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=487578

extract the month of a date
 

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


JMay wrote:
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"
wrote in message
news:Bryan.Hessey.1yy79a_1132746601.0002@excelforu m-nospam.com...

Rightmouse and Format Cell to Custom format = mmm

then =Month(A1)

shoud give you your required answer.



gireesh Wrote:

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

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

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

Thank you.


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile:
http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=487578

extract the month of a date
 

Yes, it only worked for Jan.

Better is

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

which accomodates the other eleven months also.



JMay Wrote:
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"

wrote in message
news:Bryan.Hessey.1yy79a_1132746601.0002@excelforu m-nospam.com...

Rightmouse and Format Cell to Custom format = mmm

then =Month(A1)

shoud give you your required answer.



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

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

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

Thank you.


--
Bryan Hessey

------------------------------------------------------------------------
Bryan Hessey's Profile:
http://www.excelforum.com/member.php...o&userid=21059
View this thread:
http://www.excelforum.com/showthread...hreadid=487578



--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=487578

extract the month of a date
 

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

"Roger Govier" wrote in message
...
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


JMay wrote:
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"
wrote in
message news:Bryan.Hessey.1yy79a_1132746601.0002@excelforu m-nospam.com...

Rightmouse and Format Cell to Custom format = mmm

then =Month(A1)

shoud give you your required answer.



gireesh Wrote:

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

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

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

Thank you.


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile:
http://www.excelforum.com/member.php...o&userid=21059
View this thread:
http://www.excelforum.com/showthread...hreadid=487578

extract the month of a date
 

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


Bryan Hessey wrote:
Yes, it only worked for Jan.

Better is

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

which accomodates the other eleven months also.



JMay Wrote:

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"
<Bryan.Hessey.1yy79a_1132746601.0002@excelforu m-nospam.com
wrote in message
news:Bryan.Hessey.1yy79a_1132746601.0002@excelfo rum-nospam.com...

Rightmouse and Format Cell to Custom format = mmm

then =Month(A1)

shoud give you your required answer.



gireesh Wrote:

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

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

As i am having a huge volume of data to work with a quicker and

easier

formula can be a great help.

Thank you.


--
Bryan Hessey


------------------------------------------------------------------------

Bryan Hessey's Profile:
http://www.excelforum.com/member.php...o&userid=21059
View this thread:

http://www.excelforum.com/showthread...hreadid=487578

extract the month of a date
 

Unfortunately Jim you are not alone . . .


JMay Wrote:
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


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=487578

extract the month of a date
 

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


JMay wrote:
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

"Roger Govier" wrote in message
...

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


JMay wrote:

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"
<Bryan.Hessey.1yy79a_1132746601.0002@excelfor um-nospam.com wrote in
message news:Bryan.Hessey.1yy79a_1132746601.0002@excelforu m-nospam.com...


Rightmouse and Format Cell to Custom format = mmm

then =Month(A1)

shoud give you your required answer.



gireesh Wrote:


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

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

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

Thank you.


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile:
http://www.excelforum.com/member.php...o&userid=21059
View this thread:
http://www.excelforum.com/showthread...hreadid=487578

extract the month of a date
 

Thanks guys,
Hope you have a great TG day.


"Roger Govier" wrote in message
...
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


JMay wrote:
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

"Roger Govier" wrote in message
...

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


JMay wrote:

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"
<Bryan.Hessey.1yy79a_1132746601.0002@excelforu m-nospam.com wrote in
message
news:Bryan.Hessey.1yy79a_1132746601.0002@excel forum-nospam.com...


Rightmouse and Format Cell to Custom format = mmm

then =Month(A1)

shoud give you your required answer.



gireesh Wrote:


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

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

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

Thank you.


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile:
http://www.excelforum.com/member.php...o&userid=21059
View this thread:
http://www.excelforum.com/showthread...hreadid=487578

extract the month of a date
 

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 Govier Wrote:[color=blue]
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


Bryan Hessey wrote:
Yes, it only worked for Jan.

Better is


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

which accomodates the other eleven months also.


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=487578