Dates

[Turi]

I have an excel spreadsheet with a date column. I want to
only grab the year from this date so I created a custom
format (yyyy) that gives me this. I then created a pivot
table from this, but it still is recognizing the complete
date. I have tried the formula =right(a1,4) or changing
the cell to text, but it gives me the julian date. Is
there any way to change the format to lose the month and
day information on my date?

Thanks in advance

 

[Paul]

I have an excel spreadsheet with a date column. I want to
only grab the year from this date so I created a custom
format (yyyy) that gives me this. I then created a pivot
table from this, but it still is recognizing the complete
date. I have tried the formula =right(a1,4) or changing
the cell to text, but it gives me the julian date. Is
there any way to change the format to lose the month and
day information on my date?

No. Formatting doesn't change what's in the cell - only how it's displayed.

You could insert an extra column with the formula
=YEAR(A1)
(change the A to whichever column your dates are in)
copied down. This would actually extract the year.

Alternatively, within the pivot table you could group the dates by year.

 

[Andy B]

Turi

Use a helper column with:
=TEXT(A2,"yyyy")

Andy.