• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Grouping PT - Date column with Format MMM-YY

P

prasaddn

Active Member
Hi,


I have a PT from data having a column with date (DD-MMM-YYYY) format, when I do grouping of this field by Months, i get the result, but shows only first 3 chars of month and format is showing as general. I need to get MMM-YY format, if not result of say Dec 2010 and Dec 2011 both shows under one Dec row :(


I also tried in grouping Months & Years, but the result is yyyy comes in separate row and below that indented comes months.


I even tried all five available report layout, but none shows me PT in mmm-yy :(


Any help?
 
Hi, prasaddn!

I'm using Excel 2010.

When selecting the field from the PT, don't you have a "Field configuration", it's the last option (in my spanish Excel "Configuración del campo")? If so, when clicking you'll find at bottom left a button for "Numeric format" ("Formato de número").

Regards!
 
Hi, prasaddn!

More exactly not when you select the field, but when you click on yet selected fields in any of the panes of ReportFilter, ColumnLabel, RowLabel, or SumValues.

Regards!
 
Hi SirJB,


I too use 2010 version, when I right click the RowLabels (my dates field), and and click the small drop down arrow, I get options like up, down etc, and move to other panes viz reportfilter, sumvalues, and columnlable.


I also see field setting (probably what you see as field configuration, but that does not give me option for numberic format :(, it shows only sub total and print & layout tab.


Any other suggestions, please?
 
May be I can reframe the requirement.


I have one column with date of hire people while in another column I have date of people fired. I am trying to make a table shwoing past 24 months and number of people hired and fired.


The past 24 months are like running months.


I thought of doing a PT and use Getpivotdata function :(
 
Hi, prasaddn!

Here's a copy of my screen: http://www.2shared.com/photo/8ngJcJqW/Grouping_PT_-_Date_column_with.html

Even it's in spanish I hope you'll understand.

I typed data in Cells A1:C6, defined in E1 a PT, and assign the fields Value to SumValues (4th pane, right sided), Name to ColumnLabels (2nd pane), and Date to ReportFilter (1st pane).

Then I clicked on field Date in ReportFilter pane, it popped up the windows titled "Configuración de campo". There I clicked on left bottom button "Formato de número", it popped up the usual window for formating cells titled "Formato de celdas". And there I chose last option "Personalizada" ("Custom" I guess) and selected "mmm-yy". Enter twice.

And that was all. When clicking on drop down list at F1 I got the dates in proper format.

Try that and let me know.

Regards!
 
Hurray I found the solution :) for my problem.


i made a column as start date and another col as end date


in first row of start col, i used formula =DATE(YEAR(TODAY()),MONTH(TODAY()),1) which gives me first date of the month.


in first row of end col, i used formula =EOMONTH(<first col>,0) which gives me last date of the month.


in second row of start col, i used formula =DATE(YEAR(<first row of end col>-32),MONTH(first row of end col -32),1)


and continue to all 24 rows.


With this I have 24 running months start date and end date.


then to count the number of hires and fires, I just used the sumproduct formula.

=SUMPRODUCT((datarange>startdate)*(datarange<enddate))


Wow!! feels great that I found solution.


I know I will get much easier solutions and tips from you all.


Regards,

Prasad DN

PS: Excuse typos and grammer

PSS: There was no need for PT at all. (formula proved powerful than PT ;))
 
Hi, prasaddn!

Glad to see you could find the way out!

Oh, you're right: I never read the problem but focused on the PT, and of course it's smarter, smoother and easier with formulas. Personally I prefer formulas first, then VBA code, and at last PT. Maybe because of the kind of data I manage: problems, solvers, not reports, not filters.

You (and all of us) will sure get easier solutions for our issues and tips form us all. I'm happy to help people (that ask politely, and work before and after posting and being posted, and give feedback -even if negative results-, and realize that this is a community and not a service provider) and I'm eager to learn (always a little more, always a little better, always little different, but always a little). And here I help, get helped, learn, give advises (teach'd be exageration): it's funny sometimes, it's challeging some other times, it's maddening few times (and you've read me in those, haven't you?), but's the result is positive.

Nice to meet you.

Regards!

PS: don't worry about typos and grammar, english isn't my native language neither, and sometimes I rest thinking "should I have written correctly? or I mixed some spanish, with french or portuguese?..." and I have to re-read again...
 
You must log in or register to reply here.
Back
Top