Search code examples
excelgoogle-sheetsunique

Unique month-year combinations in excel/google spreadsheets

I have one column (A) with dates and I want to create a column with unique month-year.

I could use the UNIQUE function but that requires creating an extra column (B) with: = DATE(YEAR(A1),MONTH(A1)) and the final column = UNIQUE(B1:B)

The question is, can I do it without creating the extra column?

Solution

  • As of 2023, both GSheet and Excel have both the UNIQUE function and a BYROW function which is better defined and easier to understand than ARRAYFORMULA. Therefore I'm proposing a new solution that works in both products:

    =unique(byrow(A:A; lambda(d; date(year(d); month(d); 1))))

    Explanation: the BYROW() function loops over all the dates in the A column, and using the LAMBDA() it constructs a column of dates which replaces, for each date, the day of the month with 1. Then the UNIQUE() function takes only the unique dates.

    -- The answer below still works in GSheet, but is a little outdated and does not work in Excel --

    Wrap the date expression in an ARRAYFORMULA:

    =UNIQUE(ARRAYFORMULA(DATE(YEAR(A1:A10), MONTH(A1:A10), 1))

    There is no UNIQUE() function in Excel, it exists only in Google Sheets. You can simulate it using an INDEX/MATCH/COUNTIF scheme of formulas, but it's certainly not as simple as the solution above.