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

1. Extract unique distinct years and months from dates - Excel 365

Excel 365 dynamic array formula in cell D3:

Explaining formula

Step 1 - Convert dates to given pattern

The TEXT function converts a value to text in a specific number format.

Function syntax: TEXT(value, format_text)

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 - List unique distinct values

The UNIQUE function returns a unique or unique distinct list.

Function syntax: UNIQUE(array,[by_col],[exactly_once])

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

becomes

UNIQUE({"Oct-2009"; "Oct-2009"; "Nov-2009"; "Nov-2009"; "Jan-2010"; "Jan-2010"; "Jan-2010"; "Feb-2010"})

and returns

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

Back to top

2. Extract sorted unique distinct years and months based on a list of dates - Excel 365

This formula lists months and years based on dates sorted from newest to oldest, unique distinct means that only one instance of each month and year is displayed.

Explaining formula

Step 1 - Sort dates

The SORT function sorts values from a cell range or array

Function syntax: SORT(array,[sort_index],[sort_order],[by_col])

SORT($B$3:$B$10,,-1)

becomes

SORT({40093; 40103; 40121; 40128; 40181; 40192; 40207; 40217},,-1)

and returns

{40217; 40207; 40192; 40181; 40128; 40121; 40103; 40093}.

Step 2 - Convert dates to given pattern

The TEXT function converts a value to text in a specific number format.

Function syntax: TEXT(value, format_text)

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

becomes

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

and returns

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

Step 3 - List unique distinct values

The UNIQUE function returns a unique or unique distinct list.

Function syntax: UNIQUE(array,[by_col],[exactly_once])

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

becomes

UNIQUE({"Feb-2010"; "Jan-2010"; "Jan-2010"; "Jan-2010"; "Nov-2009"; "Nov-2009"; "Oct-2009"; "Oct-2009"})

and returns

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

Back to top

3. Extract unique distinct years and months from dates - earlier Excel versions

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

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

Recommended articles

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.

Back to top

Get *.xlsx file

Extract unique distinct year and months from dates.xlsx

Dates category

Create a date range [Formula]
Question: I am trying to create an excel spreadsheet that has a date range. Example: Cell A1 1/4/2009-1/10/2009 Cell B1 […]

Find the most recent date that meets a particular condition
This article demonstrates how to return the latest date based on a condition using formulas or a Pivot Table. The […]

Formula for matching a date within a date range
This article demonstrates how to match a specified date to date ranges. The image above shows a formula in cell […]

Unique distinct values category

5 easy ways to extract Unique Distinct Values
First, let me explain the difference between unique values and unique distinct values, it is important you know the difference […]

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

VLOOKUP – Return multiple unique distinct values
This article shows how to extract unique distinct values based on a condition applied to an adjacent column using formulas. […]

Excel categories

Comments (8)