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