Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
counting records by month and year / date format issues
I have data I've exported that is in format mm/dd/yyyy. I'm trying to
pivot to count the number of records per mmm-yyyy. Is there a way to change or convert the data of the date--not just the format--to dd-mmm- yyyy such that I can then add a formula to pull the middle and end (using MID function) to just give me the mmm-yyyy for my pivot? Or an esier way if anyone knows that achieves the same endpoint would be greatly appreciated. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
counting records by month and year / date format issues
On Mar 21, 3:54*pm, Barbara Sabatino wrote:
I have data I've exported that is in format mm/dd/yyyy. *I'm trying to pivot to count the number of records per mmm-yyyy. *Is there a way to change or convert the data of the date--not just the format--to dd-mmm- yyyy such that I can then add a formula to pull the middle and end (using MID function) to just give me the mmm-yyyy for my pivot? *Or an esier way if anyone knows that achieves the same endpoint would be greatly appreciated. Hi Barbara: If you have dates in column A like: 2/1/2006 12/13/2007 6/18/2007 1/8/2002 9/5/2005 12/8/2000 4/6/2008 3/19/2004 6/17/2006 6/11/2002 5/18/2001 7/17/2004 4/5/2001 12/5/2009 12/7/2009 12/14/2000 7/19/2006 6/18/2003 8/7/2010 11/4/2004 then in B1 enter: =YEAR(A1) &TEXT( MONTH(A1),"00") and copy down to see: 2/1/2006 200602 12/13/2007 200712 6/18/2007 200706 1/8/2002 200201 9/5/2005 200509 12/8/2000 200012 4/6/2008 200804 3/19/2004 200403 6/17/2006 200606 6/11/2002 200206 5/18/2001 200105 7/17/2004 200407 4/5/2001 200104 12/5/2009 200912 12/7/2009 200912 12/14/2000 200012 7/19/2006 200607 6/18/2003 200306 8/7/2010 201008 11/4/2004 200411 You can pivot by the column B values |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
counting records by month and year / date format issues
On Mon, 21 Mar 2011 12:54:21 -0700 (PDT), Barbara Sabatino wrote:
I have data I've exported that is in format mm/dd/yyyy. I'm trying to pivot to count the number of records per mmm-yyyy. Is there a way to change or convert the data of the date--not just the format--to dd-mmm- yyyy such that I can then add a formula to pull the middle and end (using MID function) to just give me the mmm-yyyy for my pivot? Or an esier way if anyone knows that achieves the same endpoint would be greatly appreciated. If your dates make up the rows of the pivot table, you can right click/ Group / select months and years. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
count month when date is in day/month/year format | Excel Worksheet Functions | |||
Format date with just month and year | Excel Discussion (Misc queries) | |||
I need date format to be set up as month/year. | New Users to Excel | |||
counting date entries by month & year | Excel Worksheet Functions | |||
Month Year Date Format | Excel Worksheet Functions |