Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Imported data changes to date format (5-1-5 changes to May 1, 2005 | Excel Discussion (Misc queries) | |||
I have a date 51231 (2005,dec,31) -I want to change to 2005/12/31 | Excel Worksheet Functions | |||
Convert alphanumerical(eg. 12.11.2005) to date as 12-Nov-2005 in e | Excel Discussion (Misc queries) | |||
Format date in excel 2000 like '31st January 2005' | Excel Discussion (Misc queries) | |||
chnage date from tuesday 4 January 2005 10:39.26 to 04/01/2005 | Excel Worksheet Functions |