Search code examples
google-sheetsuniquearray-formulas

Unique month+year combinations in Google Sheets

I have a column of dates in Google Sheets, and I want to create a list of the month+year combinations. I did a search and found this formula that works but when there's a blank cell in the date column then it adds the date 01/12/3799 into the list of month+year combinations.

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

How can I make this formula work without producing that odd date? Thanks.

Solution

  • I found the problem comes down to the YEAR and MONTH functions. They treat blank cells as numbers, blank month=12 and blank year=3799.

    This formula does the job:

    =UNIQUE(filter( ARRAYFORMULA(if(isblank(A1:A10),,DATE(YEAR(A1:A10),MONTH(A1:A10),1))) , ARRAYFORMULA(if(isblank(A1:A10),,DATE(YEAR(A1:A10),MONTH(A1:A10),1))) <>""))