Formula to return unique dates

Question

Sheet 2 has a table of Dates and numbers. Dates are in column A,

Date	Sample #
04-Jan-22	1
04-Jan-22	2
04-Jan-22	3
04-Jan-22	4
04-Jan-22	5
04-Jan-22	6
22-Jan-22	1
22-Jan-22	2
22-Jan-22	3
03-Feb-22	1
04-Feb-22	1
08-Feb-22	1
08-Feb-22	2
08-Feb-22	3
08-Feb-22	4
09-Feb-22	1
09-Feb-22	2
09-Feb-22	3

I would like to create a formula on Sheet 1 (Summary) to return all the unique dates.

eg. 04-Jan, 22-Jan, 03-Feb, 04-Feb, 08-Feb, 09-Feb

Answer 1

Since you have Excel 2019, you'd still have to use the formulas from my previous reply. Let's say the formulas are in D2:D19.

You can then use

=TEXTJOIN(", ",TRUE,TEXT(D2:D19,"dd-mmm-yy"))

to combine the results in a single cell. You'll probably have to confirm this formula with Ctrl+Shift+Enter too.

Once you have created the TEXTJOIN formula, you can hide the column with the auxiliary formulas, if you wish.

Answer 2

Is there a way to return the unique dates into one cell as a list?

Answer 3

Let's say the dates are in A2:A19, and you want to return the unique dates in D2 and down.

Enter the following formula in D2 and confirm it by pressing Ctrl+Shift+Enter to turn it into an array formula.

=IFERROR(INDEX($A$2:$A$19,MATCH(0,COUNTIF($D$1:D1,$A$2:$A$19),0)),"")

Format D2 as a date, then fill down as far as you want.

Answer 4

Microsoft Office Standard 2019.

I do not have the Sort or Unique functions available.

Answer 5

Do you have Microsoft 365 or Office 2021? If so, enter the following formula in the first cell of the output range:

=SORT(UNIQUE('Sheet2!A2:A19))

(Adjust the range as needed)