How to convert a full date into a year

W

Wim

Hi,

Can someone point me out how to extract a year from full dates? For example
I have a column with a lot of different dates, 05/02/1996, 07/12/1996,
04/09/1997, 03/06/1998, etc., and I want to convert them into a column with
just the years, so: 1996, 1996, 1997, 1998, etc.
I've tried this with adjusting the cell properties, and this works fine, but
when I try to make a turntable based on those year-cells, Excel still sees
them as the whole date and doesn't accumulate the items attached to them in
other columns. Perhaps this can be done easily with VBA, but I'am a newbie
at that, so any help would be appreciated very much.

TIA,

Wim
 
S

skewey

as MS Excell sees dates as number try using an if
function in your worksheet so that it returns the year
of the date value lies between the number representing
the first day of the year and the last. Alternatively
use a vlookup and construct a table again using the first
and last days of the year in question to provide the
lookup value.

Regards.
 
D

Debra Dalgleish

You can also do this without adding a column to the source data --

In the pivot table, add the Date field to the Row area
Right-click on the Date field button
Choose Group and Show Detail> Group
From the list, choose Years
Click OK
 
Top