Convert Dates into four digit YEAR ONLY

  • Thread starter Thread starter Richard
  • Start date Start date
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!
 
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.
 
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.
 
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.
 
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
 
Hi

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

Arvi Laanemets
 
After selecting the values, choose the "Text to Columns" item from the
"Data" menu.
 
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...
 
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.
 
Back
Top