Convert Dates into four digit YEAR ONLY

R

Richard

I have a database of 20,000 transactions, each of which
contains a date. I want to create a pivot table which
summarizes the dollar value of the transactions by year.
Problem is that the pivot table looks at the date and
doesnt see a year--it sees day, month and year, so it
will not group by year.

I have tried to re-format the date to just a 4 digit
year, this does not work. I have tried cutting
and "paste special" - values only, and this doesn't work--
Excel ALWAYS sees the Julian number, not the year. Help!
 
J

JE McGimpsey

If your dates are in "Julian" format, e.g., 20050117, you can convert
them to XL dates by selecting the dates and choosing Data/Text to
Columns. Click Next, Next, then choose YMD from the Date dropdown and
click Finish.

You will then be able to group by year in the PT.
 
G

Guest

I am so sorry -- I used the incorrect terminology. The
dates are in the Excel format; for example, 36696 is June
19, 2000. I cannot get Excel to just forget about dates -
- no matter what I do, it always remembers the Excel
date --36696. Any more help? Sorry for my wrong
terminology.
 
J

JE McGimpsey

I'd suggest that if the values are date serial numbers, reformat them as
dates (or use a helper column to copy the serial numbers and format the
helper column). Then use the date-formatted values in your PT. If you
use a helper column, you can hide it in your data sheet.
 
R

Rob

JE,

Can you elaborate a little more:

Where do you choose "Data/Text to Columns"? I always did this the hard
way with a formula

Rob
 
A

Arvi Laanemets

Hi

You can get a year number into helper column, using formula like
=YEAR(A2)
where A2 contains a date

Arvi Laanemets
 
J

JE McGimpsey

After selecting the values, choose the "Text to Columns" item from the
"Data" menu.
 
R

Richard

Thank you!
-----Original Message-----
I'd suggest that if the values are date serial numbers, reformat them as
dates (or use a helper column to copy the serial numbers and format the
helper column). Then use the date-formatted values in your PT. If you
use a helper column, you can hide it in your data sheet.




.
Click to expand...
 
T

Tanya

I am so sorry -- I used the incorrect terminology. The
dates are in the Excel format; for example, 36696 is June
19, 2000. I cannot get Excel to just forget about dates -
- no matter what I do, it always remembers the Excel
date --36696. Any more help? Sorry for my wrong
terminology.
Click to expand...
keeps all dates as serial #'s, so even when formatted to appear as June
19, 2000, the value it maintains is 36696.
 
Top