## Extract unique distinct year and months from dates

*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

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

How to create a list of random unique numbers

Question: How do I create a random list of unique numbers from say 1 to 10, without using VBA and […]

Formula for matching a date within a date range

Table of contents Match a date when a date range is entered in a single cell Match a date when […]

The image above demonstrates an array formula in cell E4 that searches for the closest date in column A to the […]

Highlight overlapping date ranges using conditional formatting

The image above demonstrates a conditional formatting formula that colors a record if there is at least one record that […]

5 easy ways to VLOOKUP and return multiple values

This post explains how to lookup a value and return multiple values. No array formula required.

Match two criteria and return multiple records

Question: I have a table of 3 columns (Security name, date, price) and I have to find the price of […]

Find latest date based on a condition

Table of contents Lookup a value and find max date How to enter an array formula Explaining array formula Download […]

5 easy ways to extract unique distinct values

You have quite a few options to choose from if you are looking for a way to create a unique […]

How to return a value if lookup value is in a range

In this article, I will demonstrate ways to lookup values that is to be found between given ranges and return […]

Find last matching value in an unsorted list

I read an interesting blog post Find Last Item in Group With Index Match written by Debra Dalgleish. It is about […]

5 easy ways to extract unique distinct values

You have quite a few options to choose from if you are looking for a way to create a unique […]

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 an unique distinct […]

Create a unique distinct alphabetically sorted list

The array formula in cell D3 extracts unique distinct values sorted A to Z, from column B to column D. […]

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

Use the img tag, like this: <img src="Insert pic link here">

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