Stupid Pivot Table question - put grouped dates into single column



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

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

Shane Devenshire


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

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


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


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.

Shane Devenshire


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.


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"

Shane Devenshire

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question