Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Excel: date converion
Excel: i need to convert date format <12/17/2004 to TEXT :< Dec 04 or <Dec
2004. I need this data so that i can make a pivot table based on Month wise data. |
#2
|
|||
|
|||
You can create a helper column for use in your pivot table
D2: =TEXT(A2,"mmdd") E2: =TEXT(A2,"yyyy") Don't use mmm or mmmm for Jan or January as they will not sort properly for your. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "pradeep-kar" wrote in message ... Excel: i need to convert date format <12/17/2004 to TEXT :< Dec 04 or <Dec 2004. I need this data so that i can make a pivot table based on Month wise data. |
#3
|
|||
|
|||
David McRitchie Wrote: You can create a helper column for use in your pivot table D2: =TEXT(A2,"mmdd") E2: =TEXT(A2,"yyyy") Don't use mmm or mmmm for Jan or January as they will not sort properly for your. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "pradeep-kar" wrote in message ... Excel: i need to convert date format <12/17/2004 to TEXT :< Dec 04 or <Dec 2004. I need this data so that i can make a pivot table based on Month wise data. Hi pradeep-kar Try this =TEXT(A1,"mmm yyyy"), this assumes your date is in cell A1, and for 12/17/2004 will return Dec 2004, if you want Dec 04 remove 2 of the y's -- Paul Sheppard ------------------------------------------------------------------------ Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783 View this thread: http://www.excelforum.com/showthread...hreadid=397734 |
#4
|
|||
|
|||
Another option is to keep your dates in the original data. But then group by
month (or month and year) inside the pivottable. pradeep-kar wrote: Excel: i need to convert date format <12/17/2004 to TEXT :< Dec 04 or <Dec 2004. I need this data so that i can make a pivot table based on Month wise data. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
today's date in excel | Excel Discussion (Misc queries) | |||
Excel Query Wizard Date Format | Excel Discussion (Misc queries) | |||
Excel formula with date constraints | Excel Discussion (Misc queries) | |||
In Excel 2003, entering date without slashes, the date is incorre. | Excel Discussion (Misc queries) | |||
Excel 2002 date formulas problem | Excel Worksheet Functions |