Author: Oscar Cronquist Article last 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


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:

=TEXT(INDEX($A$2:$A$135, MATCH(0, COUNTIF($C$1:C1, TEXT($A$2:$A$135, "mmm-yyyy")), 0)), "mmm-yyyy") + CTRL + SHIFT + ENTER

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:

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

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:

Count unique distinct months

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