## Extract unique distinct year and months from dates

**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

### Table of Contents

## 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"}.

## 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"}.

## 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

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

### Dates category

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

This article demonstrates how to return the latest date based on a condition using formulas or a Pivot Table. The […]

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

### Unique distinct values category

First, let me explain the difference between unique values and unique distinct values, it is important you know the difference […]

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

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

### Excel categories

### 8 Responses to “Extract unique distinct year and months from dates”

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

How to Get in Sorted Order (ASC or DESC)?

Thanks!

Qadeer Ahmed,

Great question. Formula in cell D2:

ASC=TEXT(SMALL(IF(COUNTIF($D$1:D1, TEXT($A$2:$A$135, "mmm-YYYY")), "", $A$2:$A$135), ROWS($A$1:A1)), "mmm-YYYY")

DESC=TEXT(LARGE(IF(COUNTIF($D$1:D1, TEXT($A$2:$A$135, "mmm-YYYY")), "", $A$2:$A$135), ROWS($A$1:A1)), "mmm-YYYY")

Thanks for helping Oscar, it works! :)

Not working for me.

The Excel file sorts from top to bottom in descending order.

The solution Oscar gave is giving errors.

Please help.

Brandon,

You need to adjust the bolded cell range to the cell above your selected cell.

=TEXT(SMALL(IF(COUNTIF(

$D$1:D1, TEXT($A$2:$A$135, "mmm-YYYY")), "", $A$2:$A$135), ROWS($A$1:A1)), "mmm-YYYY")Did that help?

=TEXT(SMALL(IF(COUNTIF($D$1:D1, TEXT($A$2:$A$135, "mmm-YYYY")), "", $A$2:$A$135), ROWS($A$1:A1)), "mmm-YYYY")

This formula is only working partially for me with errors.

Wouldn't a simpler formula be:

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

Stuart,

yes, Excel 365 has great new functions.