I have a data table that has a column called "Received Date" (which includes Hour & Min). Is it possible to create a calculated field using this coulmn to get the "Received Year" (=Year(Received date)) and "Received Month" and use these fields as a filter for the Pivot Table? Any assitance much appreciated. Thanks.
Last edited by moratufernando; 01-02-2013 at 12:45 AM.
Is this what you're up to.
See the attached file.
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
Thank you for your response.
Yes I understand that additional columns can be created and such columns used for the Pivot table.
However as the data table is regularly updated via a download from a CRM system, I would like to avoid that.
In the Options toolbar appearing under Pivottable Tools (you need to create a pivot table to see this), there is a "Formulas" button and a "Calculated field" option under that. However when I create a new field within the Pivot table, Excel does not allow me to use that field as a filter. I was trying to see whether there was a way this could be made possible.
If you have to do this often, you can use VBA to add this formula in the table.
Thanks, I'll follow that up. I'll have to get help asVBA is out of my league.
Maybe you can post an excel example, without confidentional information.
Add also the desired result.
Then I will take a look, if I can help you.
if it's a real date field add it to the row field area then right-click and group it by year and month which will create an additional field for the year. you can then add either or both fields to the filter area
Josie
if at first you don't succeed try doing it the way your wife told you to
Thank you very mch for the offer. I will first try the suggestion of JosephP and if that does not deliver what I need I will take you up n your offer. Much appreciated.
Thanks. I will try this and get back to you. Cheers!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks