Author: Oscar Cronquist Article last updated on November 15, 2018

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:

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

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

Recommended articles

test
How to use Pivot Tables – Excel’s most powerful feature and also least known
A pivot table allows you to examine data more efficiently, it can summarize large amounts of data very quickly and is very easy to use.

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.

Get *.xlsx file

Extract unique distinct year and months from dates.xlsx

Recommended article:

Recommended articles

Recommended articles

test
Count unique distinct months
The formula in cell D18 counts unique distinct months in cell range B3:B16. Formula in D18: =SUMPRODUCT((FREQUENCY(DATE(YEAR($B$3:$B$16), MONTH($B$3:$B$16), 1), DATE(YEAR($B$3:$B$16), […]

Recommended articles

test
Filter unique distinct values based on a date range
Table of Contents Filter unique distinct values based on a date range How to enter an array formula Filter unique […]

Recommended articles

test
List all unique distinct rows in a given month
This article demonstrates a formula that extracts unique distinct records/rows for a given month specified in cell C2, see the […]