| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| extract name when a date in another cell gets near | Excel Worksheet Functions | |||
| Get month from date | Excel Discussion (Misc queries) | |||
| How do I break a date range by month? | Excel Discussion (Misc queries) | |||
| Round date to first of month | Excel Discussion (Misc queries) | |||
| HELP with this function | Excel Worksheet Functions | |||
Linear Mode
