Last Refresh date for pivot table
 

Good morning. I have a spreadsheet that contains a pivot table and am
wondering if there is a formula that can be used at the top of the
spreadsheet to provide the last date the pivot table was refreshed? Any help
is much appreciated.

Thanks

Answer: Last Refresh date for pivot table
 

Displaying Last Refresh Date for a Pivot Table in Excel

1. Click on any cell within the pivot table.
2. Go to the PivotTable Analyze or Options tab in the ribbon.
3. Click on Options in the Data group.
4. In the PivotTable Options dialog box, go to the Data tab.
5. Check the box next to Refresh data when opening the file if it is not already checked.
6. Click on the OK button to close the dialog box.
7. Go to the cell where you want to display the last refresh date.
8. Type in the following formula:

1. Note: Replace "PivotTable Name" with the actual name of your pivot table.

9. Press Enter to display the last refresh date.

This formula uses the GETPIVOTDATA function to retrieve the last refresh date from the pivot table. The "Last Refresh" argument is a special field name that Excel recognizes and returns the date and time of the last refresh.

Last Refresh date for pivot table
 

Select a cell in the pivot table then Choose toolsmacrosvisual basic editor
[alt+f11] and type in the immediate window:
Activecell.PivotTable.RefreshDate
followed by enter.

Last Refresh date for pivot table
 

Perhaps this User Defined Function (UDF):

Hold down the [ALT] key and press [F11]
....(to see the Visual Basic Editor)

Locate your workbook, in the left window list
Right-Click on it and select: Insert Module

Make sure the first line at
the top of that module is: Option Explicit

Then copy the below UDF code and paste it into that window
(anywhere under: Option Explicit)

Public Function LastPvtUpdate(rngCell As Range) As Variant
Dim cPvtCell As Range
Dim pvtTbl As PivotTable
On Error Resume Next
Set pvtTbl = rngCell.Cells(1, 1).PivotTable
If Err.Number = 0 Then
With pvtTbl
LastPvtUpdate = .RefreshDate
End With
Else
LastPvtUpdate = "Error: No Pivot Table Reference!"
End If
End Function

Now switch to the worksheet that has the Pivot Table.

Assuming your pivot table begins in cell B10...

This formula will return the last update date/time stamp
for the referenced pivot table data:
B9: =LastPvtUpdate(B10)

Format that cell as date/time
<format<cells<number tab
Category: Time
Type: (select an appropriate date/time format)

OR....you could use something like this:
="Last refreshed: "&TEXT(LastPvtUpdate(B10),"m/d/yy h:mm AM/PM")

Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"HRobertson" wrote in message
...
Good morning. I have a spreadsheet that contains a pivot table and am
wondering if there is a formula that can be used at the top of the
spreadsheet to provide the last date the pivot table was refreshed? Any
help
is much appreciated.

Thanks