PIVOT TABLE DATE

[Guest]

I need to create a header that includes a date that will update each time a
pivot table is opened--which I know how to do-- but how do I also include a
"last date refreshed"

In other words, when I first open this pivot table, I want to see
yesterday's date (last date opened) and today's date. Of course, sometimes I
will be opening it on Monday, and the last date opened will be the previous
Friday. Any help??

Thanks,

 

[Mallycat]

update each time a
pivot table is opened....but how do I also include a "last date
refreshed"

Any data like this will need VBA, because you can't guarantee 'when'
the last day of access was. I assume you have set the Pivot table to
Auto refresh when opened. Hence all you need is the last date opened.
A simple VBA macro like this will do it.

Sub auto_open()
Sheets("sheet1").Range("A1").Value = Now()
End Sub

Matt

 

[Debra Dalgleish]

You can use programming to show the date of the last refresh.

On a regular module, add the following function:

Function PTRefresh(rng As Range) As Date
PTRefresh = rng.PivotTable.RefreshDate
End Function

On the worksheet, use a formula to display the date, e.g.:

="Last refreshed " & TEXT(ptrefresh(A4),"dd-mmm-yy hh:mm")

where A4 is a cell in the pivot table.