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.
Rightmouse and Format Cell to Custom format = mmm
then =Month(A1)
should give you your required answer.
Originally Posted by gireesh
Last edited by Bryan Hessey; 11-23-2005 at 07:53 AM.
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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
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
news:[email protected]...
>
> 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
>
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.
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" <[email protected]>
> wrote in message
> news:[email protected]...
>
>>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
>>
>
>
>
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" <[email protected]> wrote in message
news:[email protected]...
> 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"
>> <[email protected]> wrote in
>> message news:[email protected]...
>>
>>>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
>>>
>>
>>
Unfortunately Jim you are not alone . . .
[QUOTE=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
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},{"jan","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"
>><[email protected]>
>>wrote in message
>>news:[email protected]...
>>
>>>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
>>
>
>
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" <[email protected]> wrote in message
> news:[email protected]...
>
>>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"
>>><[email protected]> wrote in
>>>message news:[email protected]...
>>>
>>>
>>>>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
>>>>
>>>
>>>
>
Thanks guys,
Hope you have a great TG day.
"Roger Govier" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>>
>>>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"
>>>><[email protected]> wrote in
>>>>message
>>>>news:[email protected]...
>>>>
>>>>
>>>>>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
>>>>>
>>>>
>>>>
>>
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.
[QUOTE=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
Bryan Hessey wrote:[color=blue]
> 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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks