Search code examples
dategoogle-sheetsfiltergoogle-sheets-formula

Filter date column in Google Sheets to retrieve only the last available date of each month

I am using Google Sheets and I have a column like this:

3/01/2024
29/12/2023
22/12/2023
15/12/2023
8/12/2023
1/12/2023
24/11/2023
17/11/2023
10/11/2023
3/11/2023
27/10/2023
20/10/2023
13/10/2023

I would like a formula to extract only the last day of each month in a new column, which would return something like:

3/01/2024
29/12/2023
24/11/2023
27/10/2023

I have been trying to play with the months and functions like =ARRAYFORMULA(IF(A$4:A<>""; EOMONTH(DATE(VALUE(RIGHT(A$4:A;4)); VALUE(MID(A$4:A;4;2)); VALUE(LEFT(A$4:A;2))); 0); "")) but they did not work as expected.

I am kind of new to Google Sheets so I don´t know what else could I do. Thanks in advance.

Solution

  • You may try:

    =sortn(A1:A13;9^9;2;eomonth(A1:A13;);)

    enter image description here