Working with Dates and Time in Pivot Tables in Excel

Hi All,

I have arranged data using a pivot table and want to display the data
using a pivot chart, which I have been able to do.

I have recorded a basic macro which should (in theroey) change the Value field in the pivot table. However when i run the macro I get the following error message:
Run-Time error 1004
Unable to get the PivotFields property of the Pivoit Table Class.
The code is as follows:
Code:

Sub Water()
'
' Water Macro

    Sheets("Sheet1").Select
    Range("I2").Select
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Count of Result"). _
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Water"), "Count of Water", xlCount
    ActiveWorkbook.ShowPivotTableFieldList = False
    Sheets("Flux Levels").Select
End Sub

When I run the deubg option the error seems to be occuring on the 3rd and 4th lines of the code.

Any suggestions?

Thanks!

The enclosed file has data added on a weekly basis. I want to show the data in 3 ways and have tried to create 3 pivot tables to do this (see Pivot Table tab). However the MONTH figure appears as a date but I want it to appear as July, August, September as per the data. Also the DELAYS appear as a number of incidents rather than the actual delay number. What am I doing wrong ? I have manually created what I want the 3 pivot tables to show under the REPORT tab. Any help is appreciated. Kevin.

Hi

I am using Excel 2010 and have a spreadsheet containing just over 2000 rows, including a date field for each record. The date info is definitely in the correct date format because I can 'Filter' by year/ month etc when I put a filter on the header row. However, when I put the data into a pivot table, the 'Date Filter' option is greyed out.

Anyone any ideas how a activate the date filter option in the pivot table?

Hi All,

I'm trying to figure out how to write a macro for changing date in a pivot table.

I have a pivot table that I update every.

The pivot table data contains accumulated data from the begining of the month.

Each day I add data from yesterday, except for Sunday, in which I add data from Thursday (my data never includ Friday and Saturday).

So if today is Sunday 22/08/2010, I added data for Thursday 19/08/2010
And I need to change the date 19/08/2010 in my pivote table after refreshing it.

This is the macro for refreshing the pivot table and chaning the date for 15/08/2010

Range("E8").Select
ActiveSheet.PivotTables("PivotTable3").PivotCache.Refresh
ActiveSheet.PivotTables("PivotTable2").PivotFields("end serv date").CurrentPage _
= "15/08/2010"

How do I make it dynamic so the macro will change the date to the next day

Thanks

I have a field that comes straight out of a sql server database where they are storing the date and time as a string in as yyyymmddhhmmssmm

that's year month day hour minute second millisecond

so Jan 4th of 2011, 8:26:08:02 PM looks like
2011010420260802

and, yes, it is definitely a string in sql server and comes across to excel as a string

so I break it up to a proper US date time like this

=IF(LEN(B2)0,DATE(MID(B2,1,4), MID(B2,5,2),MID(B2,7,2)) + TIME(MID(B2,9,2), MID(B2,11,2),MID(B2,13,2)),"")

pulling out chars and using them in the DATE and TIME functions

then I format the cell like this

[$-409]m/d/yy h:mm:ss AM/PM;@

it works fine, this is what is displayed
1/4/11 8:26:08 PM

but, when I make a pivot table this is what gets displayed
1/4/2011 20:26

I don't want that;
I want it formatted the way it is on the data sheet

I can't find a way to keep the format in the pivot table

perhaps the problem is with my formula for making a date time out of the string ?

Hi,

Working in excel 2007. I have set up a pivot table to view sales figures.

I download the data from an sql server and then have to run a text to columns operation so that the date is in the correct format and an can be sorted.

When I built the pivot table from scratch, when I chose to filter by 'Creation date' there was no issue and I could select data from this year, last month, this week-no problem. I test once or twice pasting in the data and seemed to have no issue. When i tried this week, the date filter is no longer possible, I can now just see label filters.

I have tried, formatting the source data in other ways but to no avail. I can filter the souce data by date with no issue but for some reason the date filters are not working on the pt.

Please help.

Dates are part of a 60,000+ row datatable I'm using in a Pivot Table. The datatable is a huge time record database: Employee Name; Date; Hours worked. I have selected the Date field as a Report Filter in my Pivot Table. When I pull down the Date field in the Pivot Table, I'd like to select a date range for the report. Instead, each unique date in my datatable is listed, and I merely have the option to click on/off each specific date. The table is 2 years of data, so I don't want to click 365 dates if I want to report the other 365 dates.

Is there an easier way to filter the Pivot Table using a date range variable?

Dave

Hi
Would appreciate some help.
I have a pivot table containing some personal information. In the first row field section I have surname and then within that a field containing some dates. For each person they may have 1,2,3 or more dates. I want to only show the latest date so I can extract this data to a seperate sheet.
I've looked at the top 10 function but that only seems to work on the first grouping (in my case surname).

Example below, so for this personal I would only want to show the 28/06/10 date.

ALLSOP 01/07/2008 02/06/2008 26/03/2008 28/06/2010

Please help!

Hi all,

I have a rather lengthy macro that concules by refreshing two different pivot tables. This macro has been in use for several months now with no issues but yesterday the user called me and stated that she was getting an error.
The error is "Run-time error '1004': The Pivot Table field name is not valid. To create a Pivot Table report, you must use data that is organized as a list with labeled columns. If you are changing the name of the Pivot Table field, you must type a new name for the field."

The potion of the code where the error occurs is on the "PivotCache.Refresh" line of the following:
Sheets("Quote_RFQ_Received").Select
ActiveSheet.PivotTables("PivotTable3").PivotSelect "", xlDataAndLabel, True
ActiveSheet.PivotTables("PivotTable3").PivotCache.Refresh
Range("B4").Select

It appears that the code doesn't like the name "PivotTable3" -- although it didn't have a problem with it before yesterday.

I found a similar thread to this on this message board but the responses were to ensure that the PT name in the code matched what is actually on the PT in the spreadsheet. I followed the different examples to determine the actual table name and I can confirm that the name in the code matches PT field name.
I also confirmed that each of the three PT columns had headers.

I tried the macro on a couple other machines (besides the daily user) and the same error occurs. Now here's the really odd thing; when I run the macro on my machine, it works fine.

Any ideas on 1) why it has worked for the past couple months and all of a sudden an error occurs, 2) why the macro works fine on my machine but not others, and most importantly 3) what is the fix.

Thanks for your help,

Shawn

I have created a Pivot Table and one of the fields contains dates.

How do I take the Min and Max date from the pivot table but I just want to show the Min and Max date on a different cell.

So for example,

CELL A:1 contains ="Date Period from " + Min(startdate) + " to " + Max(startdate)

How do I get this working correctly?

I've been tasked with the creation of a spread sheet that will track the job launch process of a manufactured product. There are many tasks within the process. A traditional spread sheet becomes clumbsy to view and enter data.

My thought was to use a Pivot Table, allowing multiple views of that data and making data entry as easy as a list. My question is, I want to display dates (1/31/99) within the data area of the pivot table. To date I've not been successful. Can this be done?

Hello,
I am attempting to create a pivot table to show counts by Week Number for three different date columns. I am struggling with the Count not working correctly (the second and third date field counts are being grouped/pivoted under the first date field).

Each individual Part (row) has a 'Received Date', 'Analyzed Date', and 'Ship Date'. I have converted the dates to 'Week Number'. I now want to display a Pivot Table that shows the Count of units by Week Number for each of these 3 date fields. Please see generic example below:

Sample Excel Sheet Layout:
Serial Number Week # - Date Received Week # - Date Analyzed Week # - Date Shipped 100000 1 2 2 200000 3 3 3 300000 2 2 3

Sample of what I would like the Pivot Table to look like:
Count of Date Received Count of Date Analyzed Count of Date Shipped Week 1 1 0 0 Week 2 1 2 1 Week 3 1 1 2

Appreciate any help you can provide.

I get this error when trying to refresh the Pivot table using either manual refresh or the code below? What the heck is causing this and how do I fix the Pivot table? Thanks.

Sub PivotStatus()
ActiveSheet.PivotTables("PivotTable3").PivotCache.Refresh
End Sub

Hello, When I create a pivot table and click the dropped for my Row Labels the Date Filter option is grayed out. The Data I am selecting for the the row label is shown as dates. I have attached a screen shot to show you what I am seeing. Any help in resolving this issue will be greatly appreciated.
Screen shot.jpg

I am creating 10 pivot tables for data in one worksheet and putting them in a separate work sheet. i used the macro recorder but the cod eit worte stop at this line

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"'RCH&BEA'!C2").CreatePivotTable TableDestination:="'RCH&BEA PIVOT TABLES'", TableName:= _
"RCH&BEA PIVOT TABLES", DefaultVersion:=xlPivotTableVersion10

The error i get is that run time error 1004 where it says this command requires atleast two rows of source data. It also says something about specifically about pivot tables but it cuts it off

Hi

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.

Hope this makes sense!! Would really appreciate any suggestions.

Many thanks
Lyn