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 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 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.
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.
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 have a pivot table with multiple row fields and multiple column fields. One of the column fields is a Date and I need some VBA that will auto-sort the columns into ascending order by the Date column field.
E.g., if the first four column labels are "2-Jun-2010, 13-May-2009, 16-May-2013, 17-May-2012" then i want the sort to arrange them as "13-May-2009, 2-Jun-2010, 17-May-2012, 16-May-2013".
Note: This is the left to right order of the columns i'm talking about, not the top to bottom order of the rows, or the data in the rows but specifically the column labels.
I've tried googling a solution and I can find a variety of code that deals with sorting the data in the rows in all sorts of ways, but nothing on how to order the columns.
i'm using excel 07, and the source data for the pivot table has the Date field formatted as custom "dd-mmm-yyyy". This can be changed if necessary.
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.
My 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?
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 a stripped down data source for debugging purposes. I only have 4 rows of data for test purposes. The dates are formatted as dates.
When I create the pivot table the dates become my column values. When I select the first date in the pivot table the Group By Field menu option is grayed out. I tried setting a tabular format but didn't work.
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 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.
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:
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)
Last week I posted a question related to formatting a cell to return a Day of the Week versus a numerical representation IE "Wed" instead of 02/20/2008 12:00AM. The solution provided worked for me:
1) Format cell to DDD MM/DD/YYYY HR:MN. Cell range (A1:A500) 2) Format destination cell with DDD. Cell range (B1:B500) 3) Destination cell (B1) = to original cell A1 4) B1 displayed data as "Wed"
However, the issue I still have is; I wanted to create a pivot table summarizing a year activity by Day of Week (in other words 7 entries for the year) and the pivot table still recognized all the MM/DD/YYYY. I ended up with a table displaying every day of the year instead of a yearly summary by Day of Week. Is there some way to strip out all the other numerical data from the new column I created to run a pivot table by Day of the Week for a whole years activity?
I have data that develops 3 to 4 pivot table each day. I would like to know if there is a way to change the date on one of the pivot table and have the other pivot tables date change to match with the first pivot table. At this time I am going to all 3 or 4 pivot table to select the correct date. The date is in the page position of the pivot table. I have attached a small sample of the data and the pivot tables.
I need to create a vba macro which will sort data according to a code it is denominated by and then will apply specific values from a code key to the data. I really need to have some help with this as although my vba is getting a bit better this is definately beyond me.
I have a pivot table that I create weekly to include the new week's data, as well as the rest of the weeks in the year. I'd like for the macro to pull all of the columns like "WE" into the pivot table so that it includes the current week. Below is what the code looks like now. Is there a way to tell the macro to pull columns based on "field name like" language? I've been able to get this far, but it doesn't loop the headers to find all of the columns that have WE headers.
Sub AddPTFields()
Dim PT As PivotTable Dim PF As PivotField Dim FldName As String Dim LastCol As Integer Dim TxtStr As String Dim wsName As String
I want to get a subtotal of columns B C and D in a pivot table. I have tried to add a calculated item to a pivot table to add columns B C and D. When I try adding a calcuted item I am getting an additional column inserted after columns B C and D. Each additional column has the previous column duplicated. I want a subtotal of column B C and D. I don't want to use the grand total function because I also have columns E through H that I don't want in the subtotal.
How can I get the columns B C and D subtotaled within the pivot table?
How can you visually format a column with multiple values in a pivot table? Say you have month in the column section, and three values in the value section: budget, actuals, variance; I want to visually seperate each month's set of data.
I have a large data set that I want to pivot on to produce various results. I can easily pivot on two columns to produce those Unique IDs where East AND West are "Yes" or "No." I want an easy to filter on results where either East OR West is "yes." I could do this with a joined (Concatenated column) such as East/West but was hoping there might be an easier solution built into Excel pivot feature.
Pivot Tables. The structure and subject-content of cells B3:D14 (Block A) is identical to those of cells F3:H14 (Block B). I want a pivot table to treat the two Blocks as if they occupied the same three columns (e.g. B3:D26).
How do I create a pivot table with multiple data columns? My fixed asset software will not let me create a report to list multiple months/ quarters. I've created a spreadsheet that I can dump each month into, but I'd like to be able to sort by G/L acct or Department. When I try to create a pivot table, I can't get it to accept each month as a data field. Ive attached a copy of the spreadsheet that I'm using & the report that I'd like it to look like.
So I am running in to a large brick wall! I have a single pivot tablet with a column for items received and then a column for items shipped. I would like to create a calculated field in the pivot table that will subtract the items received from the items shipped columns. I have attempted to add the calculated field using the tool bar but it keeps giving me "0" for every item on the pivot table when there are obvious differences.
