Format date as Custom YYYY yields 2005, but fx still shows 1/1/2005
 

The only way I've figured out how to get the fx line to read 2005 is
by saving the xls file as a text file, closing Excel and reopening
through the text import wizard. I though I could format it as Custom
= YYYY then copy/paste special and choose Values, but the fx line
remains 1/1/2005. I'm trying to create a pivot table based on yearly
amount totals, but when I drag the Date field into the Column Fields
it's creating columns based on days, not years.

Instead of showing the following
2005 2006 2007

It shows
2005 2005 2005 2005 2005 2005 2005 2005
2005 2005

One column per unique mm/dd/yyyy.

Is there an easier way to do this than saving as a txt file and going
through the import wizard?

Gracias,
El Capitan de los Snuggles

Format date as Custom YYYY yields 2005, but fx still shows 1/1/200
 

If you use a help column with:

=TEXT(A1,"yyyy")
or
=YEAR(A1)

then you will have a field that you can use. You can also edit /
pastespecial values if you like
--
Gary''s Student - gsnu2007a


"Captain Snuggles" wrote:

The only way I've figured out how to get the fx line to read 2005 is
by saving the xls file as a text file, closing Excel and reopening
through the text import wizard. I though I could format it as Custom
= YYYY then copy/paste special and choose Values, but the fx line
remains 1/1/2005. I'm trying to create a pivot table based on yearly
amount totals, but when I drag the Date field into the Column Fields
it's creating columns based on days, not years.

Instead of showing the following
2005 2006 2007

It shows
2005 2005 2005 2005 2005 2005 2005 2005
2005 2005

One column per unique mm/dd/yyyy.

Is there an easier way to do this than saving as a txt file and going
through the import wizard?

Gracias,
El Capitan de los Snuggles

Format date as Custom YYYY yields 2005, but fx still shows 1/1/2005
 

You mean the formula bar? The formula bar shows you how Excel interprets the
entry, which may be different than how Excel displays the entry.

Also, in a pivot table, you can group a date field so that it combines all
days in a given year together into a single entry for that year.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Captain Snuggles" wrote in message
oups.com...
The only way I've figured out how to get the fx line to read 2005 is
by saving the xls file as a text file, closing Excel and reopening
through the text import wizard. I though I could format it as Custom
= YYYY then copy/paste special and choose Values, but the fx line
remains 1/1/2005. I'm trying to create a pivot table based on yearly
amount totals, but when I drag the Date field into the Column Fields
it's creating columns based on days, not years.

Instead of showing the following
2005 2006 2007

It shows
2005 2005 2005 2005 2005 2005 2005 2005
2005 2005

One column per unique mm/dd/yyyy.

Is there an easier way to do this than saving as a txt file and going
through the import wizard?

Gracias,
El Capitan de los Snuggles