Excel Date/Pivot Table Problems

[John D. Jones]

I have a list of dates and an associated number for each date. (e.g.

05/11/95 10
10/11/95 11
11/11/95 14

etc., etc.)

I can construct a pivot table to summarise the data into months, years
etc. but I can't expand the data to include the dates that are not listed
(e.g. 6/11/95). Does anyone know how to construct a pivot table that will
list ALL dates even though some dates are missing from the table? Failing
this, can anyone suggest the most efficient way to expand the data,
listing every date, so I can then display this data in a pivot table?

Thanks in advance

John

[John Walkenbach]

John D. Jones (john_d...@gbccmail.lehman.com) wrote:
: I have a list of dates and an associated number for each date. (e.g.

: etc., etc.)

I've run across this problem myself. The only solution I know of is to
insert dummy records in the database for the missing time periods.
Depending on your app, this may or may not be a viable solution.

John Walkenbach
jw...@crash.cts.com

[Lyn Jolliffe]

"John D. Jones" <john_d...@gbccmail.lehman.com> wrote:
>I have a list of dates and an associated number for each date. (e.g.
>
>05/11/95 10
>10/11/95 11
>11/11/95 14
>
>etc., etc.)
>
>I can construct a pivot table to summarise the data into months, years
>etc. but I can't expand the data to include the dates that are not listed
>(e.g. 6/11/95). Does anyone know how to construct a pivot table that will
>list ALL dates even though some dates are missing from the table? Failing
>this, can anyone suggest the most efficient way to expand the data,
>listing every date, so I can then display this data in a pivot table?
>

>Thanks in advance
>
>
>John

As far as I know, you can't put data into the pivot table unless it
appears in the source database.

To add the dates, I would start a new table, type in the first date, and
use the fill handle to fill with the sequence of dates (the fill handle
is at the bottom right of the cursor square - drag it either with or
without the CTRL key).

Now use VLOOKUP to transfer the data from your original table. Off the
top of my head something like this should do

IF(ISERR(VLOOKUP(newdate, firsttable, 2, FALSE)), 0, VLOOKUP(newdate,
firsttable, 2, FALSE))

In other words, look up the date on the new table in the old table. If
it doesn't exist in the old table return zero, if it does exist then
return the contents of the second column. You can use this to transfer
any number of columns from the first table.

Don't forget to copy and paste values before you delete your original
table!

HTH

Lyn