Filtering by quarters of years in Excel

[Fatma_Hacioglu (Fhacioglu)]

I have a large dataset in Excel, and this dataset contains date information in the format “11/04/2023 00:00:00”. I want to filter the data in this dataset by years and quarters of years. For example, I want to filter data for 2023 and 2024 and quarters of years such as 2023Q3, 2023Q4, 2024Q1, 2024Q2, 2024Q3, 2024Q4.

In fact, first of all, I need to find out how many different years and how many different year quarters there are in the dataset I have.

Is there anyone who can help with this?

[Yoichi]

Hi,

How about the following sample?

dictQuarter = New Dictionary(Of Int32,String)From{{1,"Q1"},{2,"Q1"},{3,"Q1"},{4,"Q2"},{5,"Q2"},{6,"Q2"},{7,"Q3"},{8,"Q3"},{9,"Q3"},{10,"Q4"},{11,"Q4"},{12,"Q4"}}

dict = dt.AsEnumerable.GroupBy(Function(r) Tuple.Create(CDate(r("Date")).Year,dictQuarter( CDate(r("Date")).Month))).ToDictionary(Function(g) g.Key,Function(g) g.ToArray)

Sample
Sample20241110-1.zip (7.4 KB)

note: please check result.xlsx

Regards,

[Fatma_Hacioglu (Fhacioglu)]

Hi,

This is a great solution! It works flawlessly when I try it with 50000+ rows of data. Thanks for your support @Yoichi

Regards,