Pivot Tables should use the year of an date

Hi,

I have a table like this:

Proj.	Proj.Nr	State	Funding	AllocationYear
BR	345	Contracted	200,-€	4.5.2013
TK	344	Contracted	300,-€	4.8.2013
KLAP	332	Contracted	3475,-€	4.5.2012
SO	347	Planned	3488,-€	4.5.2011

I would like to build an pivot table like this:

State	2011	2012	2013
Contracted	0	3475,-€	500,-€
Planned	3488,-€	0	0

But currently I get this:

State	2011	2012	2013	2013
Contracted	0	3475,-€	200,-€	300,-€
Planned	3488,-€	0	0	0

The reason is, that I have to two projects they start on different dates in the same year. Is it possible to use just the year to make the column calculation, not the full date?

Thanks and regards,
flambert

Originally Posted by oeldere

f2 = year(e2) and drag down.

then you will be able to select the year in the pivot table.

That works fine. But I have to load the data each day. So the question is, if it is possible to make this step more automaticaly with the pivot-table itself.

If this is the onliest way I would do this. I load it by a vba-macro. So it is possible to insert a "=year()"-Column.

Thank you!