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:

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.

How to use 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.

Get *.xlsx file

Extract unique distinct year and months from dates.xlsx

Recommended article:

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), […]

Count unique distinct months

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 […]

Filter unique distinct values based on a date range

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 […]

List all unique distinct rows in a given month

Weekly Blog EMAIL

[newsletter_signup_form id=1]
Welcome! I am Oscar and here to help you out.

Feel free to comment and ask Excel questions.

Make sure you subscribe to my newsletter so you don't miss new blog articles.

Related articles

Find min and max unique and duplicate numerical values

Table of Contents Extract largest duplicate number Extract largest duplicate number - Excel 365 Extract smallest duplicate number Extract smallest […]

How to count unique distinct values based on a date

The array formula in cell D3 calculates the number of unique distinct items based on the given date in column B. […]

Extract a unique distinct list from two columns

Question: I have two ranges or lists (List1 and List2) from where I would like to extract a unique distinct […]

Filter duplicate values from a range that begins with string

The array formula in cell B10 extracts duplicate values from cell range B2:D4 if they begin with the condition specified […]

Calculate machine utilization

Question: I need to calculate how many hours a machine is utilized in a company with a night and day […]

Unique distinct list from a column sorted A to Z

Question: How do I create a unique distinct list from a column sorted A to Z using array formula? Array […]