Extract Unique Year and Month

[cyliyu]

I have one table column that contains a list of date.
I wanted to export each year and month to another table and ignore duplicated Year/Month.

i.e. If I have 01/02/2024, 02/02/2024 , 15/02/2024 and so on, the result should have only one row that contain year 2024 and month Feb

Please see the sample in the attached file and the expected results.

 

[vletm]

cyliyu​

I have one table column that contains a list of date.
Your expected results seems to be more than You've wanted.
I wanted to export each year and month to another table and ignore duplicated Year/Month.
How about this sample?

 

[cyliyu]

Thanks, vletm.

I apologize for not mentioning that I'm seeking help of a formula rather than a pivot table.

This is because I have numerous columns with other information that users must enter after the Year and Month have been extracted out to another table.

 

[vletm]

cyliyu​

Question:
How do You prevent that Year & Month rows will keep same after You've gotten those .. someway?
Could Your expected results has only one column ... eg Feb-2024?

As written in Forum Rules

• When drafting a question, try and lay out the question in a clear and concise way.
• Try and tell the readers what is the situation, what have you tried and what do you want to achieve.

 

[p45cal]

In the attached, formula in single cell N3:

=LET(theDates,Table1[Date],myDates,TEXT(UNIQUE(DATE(YEAR(theDates),MONTH(theDates),1)),"yyymmm"),HSTACK(SEQUENCE(ROWS(myDates)),LEFT(myDates,4),RIGHT(myDates,3)))

Also Power Query table at cell J2. If data changes in source table, then right-click the results table and choose Refresh.

 

[cyliyu]

In the attached, formula in single cell N3:

=LET(theDates,Table1[Date],myDates,TEXT(UNIQUE(DATE(YEAR(theDates),MONTH(theDates),1)),"yyymmm"),HSTACK(SEQUENCE(ROWS(myDates)),LEFT(myDates,4),RIGHT(myDates,3)))

Also Power Query table at cell J2. If data changes in source table, then right-click the results table and choose Refresh.

Thanks, p45cal.

 

[cyliyu]

Hi p456Cal,

How can I modify this formula so that it works in a Excel Table? Otherwise, I'll receive a #SPILL! error.

=IFERROR(TEXT(UNIQUE(FILTER(DATE(YEAR(Table1[Date]), MONTH(Table1[Date]), 1), Table1[Date]<>"")), "yyymmm"), "")

 

[p45cal]

Dynamic array formulae which return multiple rows don't work in tables.
Why does your result need to be in a table?

 

[cyliyu]

Noted with thanks.
I am using Table most of the time.
I will stick to range.

Thanks again for your help.