I have a pivot table with dates in the row filter as listed below. I want to group by month. For some reason when I select one of the dates and try to click the Group Field button in the pivot table tools menu it is blanked out and will not allow me to do so.
4/1/20085/1/20086/1/20087/1/20088/1/20089/1/200810/1/200811/1/200812/1/20081/1/20092/1/20093/1/20094/1/20095/1/20096/1/20097/1/20098/1/20099/1/200910/1/200911/1/200912/1/2009
I have a database of roughly 9000 rows. My first sheet is this data in a raw format (Masterdata!). Each row is a fire-rescue response call and therfore has a time stamp. The raw data uses different formats to provide the time stamp. My second sheet is my filtered data (Filtereddata!). In order to make these time stamps uniform I have a formula that looks at the Masterdata! timestamp column and if it is in a numeric format changes it to a text date and if it is a text date perserves it.
=IF(ISNUMBER(Masterdata!K:K),TEXT(Masterdata!K:K,"dd/mm/yyyy hh:mm"),Masterdata!K:K)
This gives me the "timestamp" which has both the incident start time and the date. I have =left and =right formulas breaking apart the date and the time. Therefore I have two columns on my Filtereddata! for the date of the incident and the time the call came in. What I would like to do is run some pivot tables and group incidents by month. I am unable to group them once I run the pivot table. I get a "Unable to group these items" (or something like this) message. I have assured multiple times that the date column is in DATE format. However, when I change the date to long date or short date I see no changes in the column so I am assuming there is something happening that I'm missing. The formula populating the date column on Filtereddata! is as such:
=LEFT(frfiltereddata[[#This Row],[Timecode]], FIND(" ", frfiltereddata[[#This Row],[Timecode]], 1))
why the Group Field option is not available in my Pivot Table. Other Pivot Tables in the same document, using data formatted the same way will let me format the data (the original data is in MM/DD/YY [h]:mm format). I have floored my data so that I can group by hour of the day, but it's not letting me do that.
View 3 Replies View RelatedHow to incorporate a calculated field for a sub group.
Here's what the data in the table looks like.
Competitive
Managed
ID
A
B
C
A
B
C
[Code] .....
I am looking to get 3 calculated fields onto the Right hand side that would essentially be the following Formula
=IF(L4"",IFERROR(D4/H4,"n/a"),"")
Thus,
1st Column
=IF(Competitive(A)"",IFERROR(Competitive(A)/Managed(A),"n/a"),"")
2nd Column
=IF(Competitive(B)"",IFERROR(Competitive(B)/Managed(B),"n/a"),"")
3rd Column
=IF(Competitive(C)"",IFERROR(Competitive(C)/Managed(C),"n/a"),"")
The other option would be how to set up conditional formatting to always take on the appearance of Column K of the pivot table. Thus, this calculated section would always appear as though a part of the pivot table and would adjust based on any pivot table items being expanded and collapsed.
I have a range of data over approx 300 rows. The first column contains dates.
I have created a pivot table and want to group the data by month. I have right clicked the table, grouped by months and selected the whole year as the date range.
For some strange reason the pivot table starts at 1/1/14 but ends at 21/2/14? It will not show the whole year.
I have date fields as column labels in a pivot table. When I try to group them I'm only given the option to group by integers and not by months, years etc.
I've had a look at the format settings of the column and they are formatted as date fields.
In building my pivot table my data that I want to show in the column area is showing up as rows stacked on top of each other. In the column section I'm trying to show Total Budgeted Amount next to Total Actual Amount but on the layout it's showing the two stacked on top of each other is there some kind of hidden key that I'm missing?
View 3 Replies View RelatedI have created quite a nice little macro that;
drills through a lot of key figures, updates a pivot chart and copies the chart to powerpoint.
However it crashes on one particular data set every time with this error:
Run-time error '1004':
Unable to set the _Default property of the pivot item class
The code where the debugger stops is the last line below here.
lngKpi = Sheets("Helpfile"). Cells(lngRow, 2)
Sheets("Charts").Select
ActiveSheet.ChartObjects("DK").Activate
ActiveChart.PivotLayout.PivotTable.PivotFields("KPI # (overall").CurrentPage = lngKpi
I can manually change the pivot chart to the keyfigure it crashes on and thereby workaround the problem in the macro. Also it works for more than 50 other keyfigures without problems.
1. I am trying to record a macro where I select a Pivot Table. But in the recorded macro "Pivot Table name" is not recorded neither the Pivot Field Property only the Range name is recorded. But on other systems (workstations) these details get recorded. Does this have something to do with excel settings?
2. I uploaded a macro enabled excel file on my company's "sharepoint" the drop down boxes present in the file get populated via a macro in "ThisWorkbook" page but sometimes these drop down boxes don't show any values. What can be the reason for this? Can't share the file because of data security policy of my organization.
I am unable to right click anywhere inside a pivot table in any sheet i... i have to create a new pivot table in a new workbook and I am unable to right click over there too.
View 1 Replies View RelatedAttached spreadsheet has the resource names and their date of joinings (Column - F2), now I would like group the date of joinings into Month & Year format. I tried text formula and then converted it back to date format but its giving incorrect results. For ex - Column F2 has 9th Sep -2013, whereas my formula in H2 is showing Sep-2014. Any way to group these dates into months in a simpler way?
Once I have this grouping done, I would like to do a pivot and sort them.
I'm trying to get the parent item name of a clicked cell (e.g. when I double click on field "Commessa 2" I'd like to get "Region 3" and pass it to another sheet)
Here is my code:
[Code] .....
When I run this code, I get the message "Unable to get the ParentItem property of the PivotItem class"
Attached Image : Capture.PNG
I am using Excel 2007 and my version of visual basic is 6.5.
I am baffled by the behavior of this code to manipulate one of my pivot tables. I am trying to set all but one of the pivot items in one of the pivot fields to not visible. Because there is a large number of items, I wish to suspend all automatic updates until all items are properly set to visible or non visible.
------------------------------------------------------
Sub SwitchBoards()
Dim BoardNew As String
Sheets("Board Parameters").Select
' Make sure we get the right value.
[Code].....
The MsgBox returns with: "Manual update is set to False" right after the instruction to set it to True!!
i need to populate a field to show the month and year as follows: the data is entered in one field like this: 01/09/2006. i need another field to populate this: Jun 06. i have tried to use a few formulae but to avail. look forward to your response
View 4 Replies View RelatedI need to extract a month from a field which looks to me like a date field, but as you can see in the file: Month(A1) returns 11/01/1900 instead of 06/11/2013.
View 4 Replies View RelatedI want to group data (rows) using month and year in date column using macro.
For example.
Date ID Amount
01/01/2013 12345 $100
02/01/2013 13452 $73
03/01/2013 12232 $50
04/01/2013 34232 $125
01/02/2013 12322 $67
02/02/2013 12345 $100
03/02/2013 13452 $73
04/02/2013 12232 $50
05/02/2013 34232 $125
01/03/2013 12345 $100
02/03/2013 13452 $73
03/03/2013 12232 $50
04/03/2013 34232 $125
I want to group rows by mm/yyyy in date column. Also I want sum amount column by month.
I have a column called Received Date (cell/column is format in dd-mm-yyyy that's found under 'Custom') which I make it into a pivot table, and GROUP it based on Year and Months.
Below is a look of it
-2012
Sep
Nov
-2013
Aug
Nov
-2014
Jan
Feb
Mar
Where the - sign is actually the expand of the year to see all the months in that particular year.
My question now is, how do I get the year from each of the month?
how do I get the "year" for the month of Nov in 2012 and 2013?
I am challenged/very frustrated with my pivot table which will not group dates (returns an error message).
In reading other posted questions, I see that my problem is caused by empty date cells. Is there a way around this?
My spreadsheet will be used daily to input Invoice details - part of which is the date of invoice. If I were to select 'refresh data', this would not be pulling through any new data as it wouldn't be in the range. I had made my range A1:K1500 to incorporate future entries, but then I have empty date cells as to date there are only 200 entries.
I have Record a Macro and assigned 2 macros to 2 different buttons.
The first button sorts the list by company name, which is working fine.
However when I press the second button, although it should sort the list by payment date, the data on the cells are interpreted as number and text separately, and then sorted accordingly, which gives an inaccurate result.
When I try to sort the list by payment date manually by clicking Data>Sort (by payment date), a warning box appears and from here I choose "sort anything that looks like a number, as a number" and everything in sorted correctly.
Is there a possible way to embed the "sort anything that looks like a number, as a number" option into the recorded macro?
Note: (The data in the payment date column are in "text" format rather than date format, this is necessary because a Pivot Tables is also used to manage the data on this list and the dates should be in text format in order to filter properly in the pivot table)
Not sure if this is possible but I have a sheet of data which is effectively a project plan. It has a list of resources, role and phase etc. I then have lots of rows which are w/c dates and under each "week" I have the amount of hours or days they work. So for each person I can record their hours etc.
I then need a Pivot which basically collates the data and displays each phase's total hours but month. There maybe multiple phases or and each person may appear in each phase
I've attached an example - Excel Problem.xlsx
I am currently working with a pivot table someone else created. The data set has dates only, in the correct format. The slicer has the dates categorized by month and excludes the dates in which there was no data.
Here is my problem: I added a few new months of data. Everything is calculating just fine in the Pivot table. However, my slicer where I should be able to pick the month now has these months listed as all their individual dates. I want to put the December dates under "December" and so on. The data that was present before I loaded anything additional still has this feature.
How do I group these dates, to be able to them as months? By the way, the grouping function is grayed out.
When I group information in a pivot table by date, and select quarters or years, it is of course based on a calendar year. Can this be done by fiscal years?? using an addin or something, where I set the start month of the custom fiscal year.
View 5 Replies View RelatedMy data has a field for "Donor Type." There are about 2 dozen types of and about half of them start with the letters CF. When I do a pivot table, instead of getting 2 dozen rows of data I'd like all the CF donors to be in the same row. Is there a way to do that without changing my data? Some sort of filter or grouping within the pivot table?
View 1 Replies View RelatedOnce i identify the PivotField, how can i filter it to be > than Date?
View 2 Replies View RelatedI have two fields in pivot table, "HATA TOPLAMI" and "SIRA NO". "Hata toplamı" is sum of items, and "Sıra no" is count of items. İ want to divide these two fields and add it to the pivot table, like "HATA TOPLAMI" / "SIRA NO". I have tried calculated field function from pivot table. İ wrote "="HATA TOPLAMI" / "SIRA NO"". But the summary is false. I want to divide Sum of "Hata toplamı" and count of "Sıra no" but instead it divides with sum of "sıra no". Is there a way to do this?
View 3 Replies View RelatedI have a problem in expanding/collapsing fields in pivot table.
I have source table with GROUP, SUBGROUP, ACCOUNT and AMOUNT. In pivot table I've put GROUP, SUBGROUP and ACCOUNT in Row labels respectivly, and AMOUNT in values. There are same names in SUBGROUP for different names in GROUP. (for example: groups are Production costs and Distribution costs and in each of them there are subgroups Personnel costs and Other costs).
The question is: Is it possible to expand only field Other costs in group Distribution costs while field Other costs in group Production costs stays collapsed?
Can I add a calculated field formulas in Excel pivot table. Such as CONCATENATE?
View 2 Replies View RelatedIhave a pivot table that takes data from a table, groups the duplicate items and adds their values up - so it shows just one entry for each item. I now want to add a calculated field in there so that I can divide one of the fields into another.
View 14 Replies View RelatedI have attached a simple test file. The data tab has two columns in the pivot range. The third column is _not_ in the pivot range, but is there to show the result I'm trying to get with the pivot.
The idea is to count the number of people whose entry dates are greater than 2/1/2009. My third data column gives a correct result, but a similar formula in the calculated field of the pivot table gives a completely wrong result (a date), where my goal is to generate a zero, a one, or a sum of zeros and ones.
It could be a formatting issue, but that would not explain why all the pivot results have the _same_ wrong answer.
I'm new to PTs and calculated fields, and expect I've made a naive mistake that an expert could easily spot.
I have a pivot table with Sales and Budget, if i need to find the difference in pivot table i think i need to use field settings, however i am not getting the result
I have attached the same file for reference