Question: How to create unique distinct year and months from a long date listing (column A)?
You can find the question in this post: Extract dates using a drop down list

Update! 2017-08-23, a smaller easier regular formula:

You can also easily extract a unique distinct list of dates with a pivot table:

Discover Pivot Tables – Excel’s most powerful feature and also least known

Explaining formula in cell

Step 1 - Convert dates into a given format

The TEXT function lets you convert a value based on a formatting code, in this case we want to convert the date into month and year.

The reason is we want to count previous values against this array to make sure no duplicates show up.

TEXT($B$3:$B$10,"MMM-yyyy")

becomes

TEXT({40093;40103;40121;40128;40181;40192;40207;40217},"MMM-yyyy")

and returns

{"Oct-2009";"Oct-2009";"Nov-2009";"Nov-2009";"Jan-2010";"Jan-2010";"Jan-2010";"Feb-2010"}

Step 2 - Count previous values

The COUNTIF function allows us to count values displayed above the current cell using an expanding cell reference.

COUNTIF($D$2:D2,TEXT($B$3:$B$10,"MMM-yyyy"))

becomes

COUNTIF($D$2:D2,{"Oct-2009";"Oct-2009";"Nov-2009";"Nov-2009";"Jan-2010";"Jan-2010";"Jan-2010";"Feb-2010"})

becomes

COUNTIF("Unique distinct list",{"Oct-2009";"Oct-2009";"Nov-2009";"Nov-2009";"Jan-2010";"Jan-2010";"Jan-2010";"Feb-2010"})

and returns

{0;0;0;0;0;0;0;0}

Step 3 - Values represented by a zero has not been displayed yet

COUNTIF($D$2:D2,TEXT($B$3:$B$10,"MMM-yyyy"))=0

becomes

{0;0;0;0;0;0;0;0}=0

and returns

{TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}

Step 4 - Divide 1 with array

1/(COUNTIF($D$2:D2,TEXT($B$3:$B$10,"MMM-yyyy"))=0)

becomes

1/{TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}

and returns

{1;1;1;1;1;1;1;1}

Step 5 - Return date value based on criteria

LOOKUP(2,1/(COUNTIF($D$2:D2,TEXT($B$3:$B$10,"MMM-yyyy"))=0),$B$3:$B$10)

becomes

LOOKUP(2,{1;1;1;1;1;1;1;1},$B$3:$B$10)

becomes

LOOKUP(2,{1;1;1;1;1;1;1;1},{40093;40103;40121;40128;40181;40192;40207;40217})

and returns 40217.

Step 6 - Convert to month and year

TEXT(LOOKUP(2,1/(COUNTIF($D$2:D2,TEXT($B$3:$B$10,"MMM-yyyy"))=0),$B$3:$B$10),"MMM-yyyy")

becomes

TEXT(40217,"MMM-yyyy")

and returns Feb-2010 in cell D3.

Download *.xlsx file

Extract unique distinct year and months from dates.xlsx

Recommended article:

Count unique distinct months

Extract unique distinct values based on a date range

List all unique events in a month