Stupid Pivot Table question - put grouped dates into single column

[JeffK]

I've spent an hour now pulling my hair out on what I think should have
a simple solution...

I have one column of dates and times, another of numerical values.
I've created a pivot table, grouped by year, month, and day, with the
average of the value calculated for each day. I need to get this data
out of the pivot table and into regular cells for another program,
with one column of dates and a second of values averaged by day.

The problem is that the Pivot Table only seems to store the dates as
text, with the year and month values only appearing once. I.e., the
first row of my PT has 2001 in the left most column, "April" in the
second column, "17-Apr" in the third column, and the averaged data in
the fourth column. The second row has "18-Apr" in the 3rd column and
the averaged data in the fourth column, and so on.

What I (think I) need is to either combine year-month-day into a
single column in the PT (while still averaging all the values for a
single day), or, repeat the year and month on every single line so I
can copy and paste and use the date() function to recreate that
field.

Seems like a simple problem but hard to explain. I greatly appreciate
any input - a long time searching online offered no help.
Thanks,
Jeff

 

[Shane Devenshire]

Hi,

First, the pivot table does not store dates as text it stores them as dates,
however, it does store the Month, Day, and Year a text when you apply
grouping.

Second, since you want to use this data outside the pivot table, you can
copy the pivot table and Paste, Special, Values.

Next you can use a formula to recreate the date

==--(N4&L4)

Where the Day field of the pasted data is in N4 and the year is in the L4.
This will work for the first row of data but will have a problem on the
second row. So suppose you are creating your formula in column K modify the
above formula to read

=IF(L4="",--(N4&YEAR(K3)),--(N4&L4))

And copy this down.

if this does not suit your needs, then copy the date field in the original
data set and paste it as a new field in the data area. Give this new field
like Date2. Then create the pivot table and add the two date fields to the
row area. Group one of the Date fields and suppress Subtotals.

 

[Herbert Seidenberg]

Excel 2007
When grouped by day, the dates are indeed text
and cannot be sorted or used as serial numbers.
A workaround is to drag the dates also into Values and
key and sort on that.
Data is extracted with a helper column,
VBA and GetPivotData()
http://www.mediafire.com/file/n2tzotl2wwe/12_31_08a.xlsm

 

[Shane Devenshire]

Hi,

if you believe that the dates are being stored as text in a pivot table then
simply ungroup the date field. This is the date field, and it is being
stored in the pivot table cache' as dates.

To prove this simply type the following formula outside the pivot table and
refer to a cell in the pivot table date column.

=ISTEXT(B5)
or
=ISNUMBER(B5)

The first one will return FALSE - the date field is not text
The second one will return TRUE - the date field is a number, and all dates
are numbers in Excel

Another test - place the cursor in the ungrouped date field and choose
Format, Cells, Number tab - the default selected format will be "Date"

Cheers,
Shane Devenshire