[SOLVED] date format with Pivot Table

[adamheon]

I have a row of data that I'm using to convert a date into just the year (using format cell = yyyy function). I'm trying to have a pivot table for the data to filter by year. However when i make the filter table and add that column into the filter it doesn't combine the years....instead each "year" is its own line. I attached a sample document.

Basically i'm trying to sort my pivot table by YEAR but i have to work with a standard date entry.

[davsth]

Only a quick answer, why dont you just make column A the year =year(c2) instead of c2 for your first cell. formats only change the appearance of data, they do not change the underlying values.

[adamheon]

i tried that first....but it comes 1905 in the cell. i couldn't get it working.

[adamheon]

all set...i had to use the formula =TEXT(C2,"YYYY") to convert the date properly.

[davsth]

or if you had formatted the cell with the year in as a number and not as a date with a number format it would have displayed 2016

for your information if you type 2016 in a cell and format as YYYY it will display as 1905