## Extract unique distinct year and months from dates

*Article updated on February 19, 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 in excel

**Answer:**

The formula in cell C2 extracts unique distinct months and years from column A.

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

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.

**Array formula in C2:**

copied down as far as needed.

Recommended articles:

Create a unique distinct list from a date range

Question: I have a large list of dates and other adjacent values. I want to create a distinct list from […]

List all unique events in a month

Question: I have a table with four columns, Date, Name, Level, and outcome. The range is from row 3 to […]

### Download excel tutorial file

unique distinct year and month.xls

(Excel2007 Workbook *.xlsx)

Recommended article:

Remove duplicates and sort dates by each row in excel

I might have missunderstood Aamers question: I have a sheet with 3000 rows of invoice dates that are out of […]

### Functions in this article:

**INDEX(**array,row_num,[column_num]**)**

Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

**COUNTIF(**range,criteria**)**

Counts the number of cells within a range that meet the given condition

**MATCH(**lookup_value;lookup_array; [match_type]**)**

Returns the relative position of an item in an array that matches a specified value

**TEXT(**value, format_text**)**

Converts a value to text in a specific number format

Recommended article:

Question: How do I count the number of unique months in excel? Answer: Array formula in C17: =SUM(IF(MATCH(DATE(YEAR(List), MONTH(List), 1), […]

Highlight overlapping date ranges using conditional formatting

How to highlight overlapping date ranges Click "Home" tab Click "Conditional Formatting" button Click "New Rule.." Click "Use a formula […]### 3 Responses to “Extract unique distinct year and months from dates”

### Leave a Reply

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

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

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

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

VBA code

[/vb]

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

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

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! :)