Pivot table to count dates from two columns

Hi, I have the same question as in this thread but I could not solve it based on the provided answer.

The question is: how from source data like this..

Project - Original Date - Real Date
Project A - 01-01-11 - 01-01-11
Project B - 01-01-11 - 01-02-11
Project C - 01-02-11 - 01-02-11

..create pivot table like this?

Date - Count of Original Date - Count of Real Date
01-01-11 - 2 - 1
01-02-11 - 1 - 2

The original thread has an attached file as a solution but I cant understand how to do it. I have these problems with it:

1. The Alt,D,P to open pivot wizard does not work. It opens Text to Column window instead. How can I open the wizard?

2. The macro button "refresh" generates new random data in the source but the pivot table does not update. When I press "refresh" button in the ribbon it gives error. Also Change Data Source button gives error.

3. How to create the "Column" field with [Orig;Real] data points? And how to create the "Value" field with all the dates in it? When I create my own pivot table from the source data I get three columns (Proj;Orig;Real) with data in them from respective columns.

I use Excel 2010, Czech version, Win7. I wanted to reply in the original thread but it wont let me to post question in other member's thread..

Thanks in advice for any help!

Re: Pivot table to count dates from two columns

Thanks a lot, that did solve the problem but I ran into new ones..

My source table is actually larger, it has about 20 columns, not just the 3. The data in the columns is of various data types(dates, values, yes/no, text, blanks..). When i normalize it, it puts all the data into one field called Values. This however limits the usability of the pivot table. For example:

If I want to group the date counts by month I can't because the Value field contains dates as well as other data types.

If I want to filter the pivot table by a certain column I can't because the column is inside the Value field.

There are many other things that would be possible with standard pivot table which aren't with a normalized one. I needed to used the normalized table to count dates from multiple columns, however, if I can't use most of the functionality of standard one, it's not really useful. Any ideas?

Attached is part of the data that I'm working on with the pivot tables. forum.ozgrid.com/index.php?attachment/37134/

Re: Pivot table to count dates from two columns

I think I'm finally getting to where I needed to be although its a bit more complicated than I expected:) One more question.. Looking at Glenn's Final Pivot table, is it possible to insert a calculated column into the PT, that would be equal to (count of Date:Received)-(count of Date:End)? i.e. for 2011 it would be 7-8=-1

Thank you again for your time and help. I really appreciate it!

Re: Pivot table to count dates from two columns

And also, is it possible to delete calculated field that I've inserted in a PT?