Unique month+year combinations in Google Sheets

0
votes

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.

1
google-sheetsuniquearray-formulas
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. I managed to figure out how to get it to return an empty cell rather than this odd date by adding in a check to see if a cell in the range is blank: =UNIQUE(ARRAYFORMULA(if(isblank(A1:A10),, DATE(YEAR(A1:A10), MONTH(A1:A10), 1)))) Now the only remaning problem is the empty cell it returns in the output list. How can I get it to not return an empty cell? – Tim B
Ok this formula does the job, but I wonder if someone has a cleaner solution to this problem? Thanks. =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))) <>"")) – Tim B
share a copy of your sheet – player0
I don't think there is much of an improvement from your last formula. You could post your solution as an answer so it can be accepted (by yourself) and upvoted by others. – Raserhin

1 Answers

0
votes

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))) <>""))