Pivot table on a formatted date returns date to origin code

**alleyb** · 01-24-2010, 04:45 PM

in a sheet I have a database with date that is pre-formatted 1/24
the database has a line by line (ovewr 7000 lines) multiple column entry since 1982 so there are 27 entries of Jan 24th - one for each year -
I need to find every Jan 24th and then extract the rest of the row. I believe if I can get this far that I can then extract the other info I require

I used Pivot Table to extract but Pivot Table seems to return the pre formatted 1/24 (Jan 24th) to its original date ie it brings in the complete database and the date column has too many keystrokes to just extract Jan 24 each year

**romperstomper** · 01-28-2010, 10:48 AM

You should be able to right-click on the date column in the pivot table and group it on month and day (don't include year) and then get a summary of all the Jan 24th data.

**alleyb** · 01-31-2010, 06:58 PM

I have tried a lot of variants and cannot find an answer based off your reply.
I have uploaded a cutdown file to see whether you could be kind enough to show me what you mean
The column I wish to base my work off is B
and to add a complication i wish to look at not just for example every April 22 (based off Col B) but every April 22 that falls on a Thursday (based off Col I )

**DonkeyOte** · 02-01-2010, 05:37 AM

To restrict based on day of week it would be easiest to ensure that Col I is actually storing the day of the week literally.

Presently the underlying values are the dates, the cells are merely formatted to show the weekday ... eg:

Sheet1!I2: =TEXT($D2,"dddd")
copied down

Once the above is done simply set Date and Day of Week as Page Fields selecting specific values of interest (eg 4/22 and Thursday)
Add remaining fields of interest to the PT as Row/Column/Data Fields as required.
(ie if you want to keep the transactions on an individual basis [no aggregation] then use Date3 as a Row Field)

**alleyb** · 02-01-2010, 08:11 PM

I have uploaded therefore the latest version following your instructions
and I added in beside the pivot tables a countif statement to allow me to know how many instances of for example the date 4/22 were positive and how many were negative

(btw Muchas Gracias Senor DonQ)

and now ask is there a way to automate the date within the pivot table to automatically update (for example I am working on the original file today Feb 1st and wish to calculate all instances that are positive and negative that relate to Feb 2nd. either via a linked cell that is manually updated or in a worse case scenario using a macro.

The reason why I wish to not go down the macro route is because I have a macro that runs on download of the file that forbids the copying/paste/print functions as I wanted to protect my data from just being lifted/copied/downloaded. After all the data I have is worth a lot of money if purchased from the correct channels

**DonkeyOte** · 02-02-2010, 05:01 AM

Still not sure I follow...

I would reiterate the point that to me at least it makes more sense to use Date & Day of Week as Page Fields rather than Row Fields in the Pivot.

Assuming you do that then regards automating the Date Value to be Today + 1...

As you infer you will need to use VBA for that, you can not automate the field given formulae are not permitted for an item / field name within a Pivot...
though if you're willing to update manually you can of course just alter the value in the Pivot field directly

The VBA itself is obviously fairly straightforward such that:

Please Login or Register  to view this content.

the above utilises the open event of the file and as such should reside within ThisWorkbook

It's not clear how the Day of Week should be affected (if at all) by this process...