Pivot Table - Date Subtotaling At Bottom Of Column?
Jan 9, 2013
I have a pivot table where I would like to add a column for the date next to each item listed in the pivot table. When I do this the pivot table sums the date, which is an incorrect number because it is adding a date to other dates. If I move the data to the row option it moves it under each item and the formatting is off. Is there a way to add the date in as a column without it sub totaling? I need the other columns to keep their totals.
I also was having issue creating a calculated field - below the total to show cost per item and then the profit. Is this possible.
I created a screen shot example to show what it is doing. (1st picture)
2nd picture shows what I would hope to achieve.
[IMG][/IMG]
[IMG][/IMG]
View 9 Replies
ADVERTISEMENT
Jul 16, 2014
On a sheet called, "Details", I have a pivot table that has three fields in the column area, Calendar type, Description, and Dates. I want to filter the pivot table based on a label filter in the Dates column. The filter should be between two dates (in D4 and D5) that are entered on another sheet and passed to the Details sheet through formulas in cells D4 and D5.
I have attached the following code to a button on a different sheet.
The code successfully filters for the employee name (which is a report filter in the pivot table) which is in a named cell.
I am having trouble with passing the start and end dates to my pivot table filter. I do not get any errors, the filter is simply blank.
Private Sub CommandButton3_Click()
Sheets("Details").Select
Dim pt As PivotTable
Dim Employee As String
Dim SDate As String
Dim EDate As String
[Code]...
View 6 Replies
View Related
Jun 30, 2014
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.
View 5 Replies
View Related
Apr 29, 2006
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.
View 2 Replies
View Related
Aug 16, 2013
I have a pivot table in the first sheet which includes the field "Date" as a column label.
In the remaining sheets, except for one, there are pivot tables based on the same underlying dataset which also include the field "Date" as a column label.
I would like to adjust the selection (i.e., exclude some dates) from the column label in the first sheet and see if it is possible to make the same adjustments automatically to the pivot tables in the remaining sheets as well.
note that the field "Date" is used as a Column label, i.e., it is not a Report filter.
View 3 Replies
View Related
Oct 5, 2011
There is pivot table for some reason the last column (YEAccts), which sums all the other columns values is exluding the 1st column for some reason. This "YEAccts" shows up fine in the pivot table "field list". And I followed the source for this and it's a table in a different tab that pulls data from an access query connection.
The name of the column that is summing up the rest of the columns is "YEAccts", but for the life of me I can't seem to find where this column is in the source table tab or even in the access query where the data is being pulled from.
How to find this "YEAccts" column? Also, why would it exclude not summing up the data in the first column?
View 2 Replies
View Related
Jun 20, 2014
I am trying to figure how to show in one pivot table a current column and a proposed column. I have 15k rows of data. My data columns are employee, month, task, hours, proposed month. I can get a table that has months as columns and tasks as rows with sum of hours. What I would like to do is incorporate the proposed month, so that it shows hours in the months by current and proposed. That way my result would be January current, January proposed columns etc. I can change the propsed months by formulae so I want to play with the proposed task month the refresh the pivot table to see the results.
View 2 Replies
View Related
Dec 27, 2012
I have a birthday list with name, client, birthday. I want to create a pivot table that groups by month. Report layout is tabular. Then within that month, name, client & birthday (in date order). So my end result would look like;
January Jim Jones Company 1/4
Then rest of January names 1/15
Etc.
Right now, I can't get the birthday to sort by date within the month.
View 1 Replies
View Related
Jun 10, 2013
Inside my raw data, there is a field for dates with the format of "mm-dd-yyyy". When generating the pivot table, the date is simply "mmmm". Where did the rest of the date field go?
View 1 Replies
View Related
Jun 12, 2014
I am having the hardest time trying to get my pivot table to sort right. I downloaded data over a 7 year period and its sorted by months (January-2005 etc). The issue I am having is excel is putting it into this format
January-2005 January- 2006 ......... February - 2005 February-2005.
I need it to sort chronologically
jan 2005, Feb 2005, March 2005...... Jan 2006, Feb 2006 etc.
What do I have to do to get this fixed. I would rather do not have to do it manually each time I need this type of data.
View 3 Replies
View Related
Jan 1, 2007
I am recording events on a day to day basis in excel using the first column as the date, second column as start time, and third column as end time. So an entry might look like 1-Jan _ 13:00 _ 13:40, in the three columns across. There will be multiple entries for each date, but the number of entries for each date will vary. Entries may also overlap in times. For example, the next row may read 1-Jan _ 13:10 _ 13:45. Some times there will be gaps such as a third row readng 1-Jan _ 14:15 _ 15:00. What I would like to do is compute the total elapsed time spent on projects for each day, not counting any overlaps. SO even though the total time spent on those three projects for January 1st is 120 minutes, I would like to know how much time during the day is spent doing work, regardless of how many projects are being done at the same time. So I would like excel to be able to tell me that on January 1st , 90 minutes of the day was spent on work. Hopefully this makes sense. I also need it to distinguish between dates so that it can tell me how much time was spent on January 1st. then January 2nd, 3rd and so on. It would be great if this ould be computed as the data is entered. Maybe It could record it on a new sheet with a column for each date and then it lists the time spent on that day in the row below it.
View 9 Replies
View Related
May 1, 2007
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.
View 9 Replies
View Related
Jun 3, 2006
I have a pivot table which contains dates in the drop down list. I wanted user to pick dates from calendar control tool rather than pivot table dropdown list.
I put the following code;
Private Sub Calendar1_Click()
If Weekday(Calendar1.Value, vbMonday) < 6 Then
Range("B3").Value = Format(Calendar1.Value, "dd/mm/yyyy")
End If
End Sub
it works for most of the dates but not all. Also it does not recognise the dates in the new month.
View 3 Replies
View Related
Jun 7, 2013
I'm trying to copy the value of one column to another, column A has date and time in this format: 6/1/2013 6:35:00 AM and I'm trying to get ONLY the dates to another column so that when I do my pivot, I can sort it out by date. I tried =A1 and I can format the column to how I want it but the value of the cell still remains in date&time. Is there any formula I can use? or should I change anything in my pivot table?
View 4 Replies
View Related
Jun 16, 2014
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‎
View 1 Replies
View Related
Jun 16, 2014
I have a question with regards to the display of the Date filter in a pivot table I am making.
As it stands, the filter function is displaying like this : date 1.JPG
I am looking for the filter to look more like this : date 2.JPG
I believe that I have all the dates in the source table formatted as "Date".
View 4 Replies
View Related
May 16, 2012
I have inherited a spreadsheet that I need to update soon. It takes data that has a date field and creates a pivot table by month. The person who created it completed it thru February. Now I have added data for March and April, but the pivot tables can not find this new data and I suspect it is because of the date formatting (I have done the Change Data Source under PivotTable Options to include all the new data).
The reason I suspect the date format is that she built a button to run a macro named "dateformat", which is not in the workbook. I have tired to manually format the date using the format painter from the February dates that the pibvot table recognizes, but I still get no results.
View 4 Replies
View Related
Feb 1, 2008
I have attache a file to use as a reference, the real file is over 10 000 rows long.
What I need is to be able to quickly make a summary of each company within a certain period (let's say monthly) and be able to calculate the sub total of the GST column. Every report that's created than needs to be saved and put into a seperate file e.g 'TFQ Sep 07'. I understand I need to use a Pivot Table but can some one just give me quick example of how I would do this?
View 2 Replies
View Related
Mar 19, 2013
On a worksheet, I created:
- a list of data
- a pivottable based on these data
When moving this worksheet this worksheet to another workbook, the pivot table can't refresh anymore. This throws an error message "Reference is not valid". To work around this problem I need to adapt the datasource. The same occurs if the list and the pivot table are on separate sheet, with the added strange behaviour that, when data an PT are split, it is not possible to move both sheet together.
This would not be a big issue if my problem had to be solved manually. The real problem is that I need to move the sheets from a C# program.
View 3 Replies
View Related
Dec 5, 2013
How do I stop a pivot table adding data together?
e.g.
1st Nov -100
1st Nov - 100
2nd Nov - 200
2nd Nov -200
I want this but instead get this:
1st Nov - 200
2nd Nov - 400
I need the pivot to split it out,
View 1 Replies
View Related
Apr 18, 2014
It's a given that formatting doesn't alter underlying values in all areas of Excel, merely changing the way cells look. But in the attached I show two pivot tables using exactly the same data with the sole exception that the first one has the date numbers formatted as numbers whereas the 2nd PT has the date numbers formatted as dates.
With the 2nd PT it will accept the Group by month functionality whereas the 1st PT won't.
On the face of it PTs seem to take more notice of the way a number is formatted when it comes to grouping rather than treating the number as a date which it can group.
View 1 Replies
View Related
Feb 18, 2014
How to filter the pivot table in the attachment by the Posted date. I only want to look at the data for dates within 30 days of today, and can't figure out how to do that manually, much less programatically. The attachement is a small sample of a huge report I receive; all I get is the pivot, which contains tons of information. I filter it down by my various criteria, but that's still hundreds of thousands of lines. Limiting to the last 30 days can make a huge difference, but I can't find the trick to it.
Example.xlsx‎
View 14 Replies
View Related
Oct 23, 2013
Have a simple Pivot table that i have grouped in 14 day buckets.
Currently, the date range looks like this:
1/21/2013 - 2/3/2013
However, I want the range to show the 'last' date only.
Is there a simple way to do this? I just can not see it in the format cells menu...
View 2 Replies
View Related
Apr 22, 2014
I have a table with business data such as units sold, revenue etc. entered by date (1/1/14, 1/2/14...) for 4 different business units. I have this data summarized in a pivot table. The pivot table is set up with the date filter as a row label. I would like to have a consolidated report on another worksheet with a drop down menu being used to control the date filter on my pivot table.
The following items would be on the drop down:
Yesterday, Month to Date, Quarter 1, Quarter 2, Quarter 3, Quarter 4 and Year to Date.
View 1 Replies
View Related
Nov 16, 2005
I'm making a Pivot table and i'd like that the different field will be displayed on each line.
here is an exampe of what i have and what i want to have
______Group____Average age
Man_____1_________16
_________2_________22
_________3_________25
_________4_________33
_________5_________48
Woman__1_________16
_________2_________22
_________3_________25
_________4_________33
_________5_________48
Result needed
______Group____Average age
Man_____1_________16
Man_____2_________22
Man_____3_________25
Man_____4_________33
Man_____5_________48
Woman__1_________16
Woman__2_________22
Woman__3_________25
Woman__4_________33
Woman__5_________48
Where can i found the option to edit? if it's possible.
View 9 Replies
View Related
Mar 19, 2007
I have a master list that is updated in real time by linked worksheets from several people. While my actual report covers 7 managers and 7 different products (“contract types” in this example), I have summarized and simplified the input in the attached file. There are three pertinent dates – the date assigned to the manager (col A), the date completed by the manager (col F), and the date approved by the executive (Col G).
These dates will span month ends and I need to be able to provide monthly reports that provide details on the number of contracts and the dollar value all contracts assigned and completed by the manager during the month (say, February) as well as any contracts assigned in any month which remain uncompleted at the current month-end (e.g. the $7,500 Smith contract assigned on Feb 21st that remains uncompleted and the Feb 26th $200 Jones contract which was uncompleted at Feb 28th, but completed in early March, in this example). Can this be done directly in a Pivot table from the full master list, or will I need to have separate sheets in my workbook where I extract the pertinent records for that month?
View 2 Replies
View Related
May 10, 2007
my table has the following fields: Zone (north, East, west, south), depots in each zone (D101, D102 in North and D201, D202 in South),product code (S101, S102...S100) then sales data for three stores 1, 2, 3.
My original table has the depot code, zone it belongs to and sales of each of the product made to each of the store per depot.
In the Pivot table, I shall need to show Branch and the Product codes on the row-side, and require store codes 1,2,3 to appear on the columns. The data area thus needs to be a summation of D101, D102 for North (for each of the product-codes) and D201, D202 for the South region.
MY PROBLEM:
I am unable to display 1,2,3 as separate columns.
View 7 Replies
View Related
May 9, 2014
I would like to compare % difference between date range through pivot table
Date RangeProductAmt
1st weekABC1000
2nd WeekABC2000
3rd WeekABC1259
1st weekXYZ3000
2nd WeekXYZ4000
3rd WeekXYZ2500
How to plot a Pivot table to compare the % difference between 3rd Week Vs 2nd Week Vs 1 Week for each product through pivot table?
View 2 Replies
View Related
Sep 28, 2011
I have to generate a report using a pivot table, that tells me what work was done on time and what was late. Entering a formula outside the pivot table sees the scheduled start date drop downs as "blank" and says they are late when they are not. I cannot seem to use the value fields when I try to create a calculation field within the table. I use 2010 at work, 2007 at home.
sep 11-17
FGHIJK10Count of WO No.
=IF(G12>=H12, I12," ")=IF(G12
View 3 Replies
View Related
Nov 11, 2013
I have a pivot table with the following items;
Report Filter = Project Names & Dates (filtered on 2 fields)
then the pivot table of data shows
Dept, Sum of Hours, Sum of Total ($), Sum of Days
When you click on the drop down to adjust the filter for the Date it gives you the whole list of dates, day by day as it is in the source data.
Is there a way to make it in the filter by month and year, the way it would in an ordinary filter. So if I wanted to have the pivot table show only the values with an October date I can, rather than having to deselect all and then individually check the boxes for the 1st through to the 31st of October.?
View 2 Replies
View Related
