Google-sheets – Get YEAR() and MONTH() from a string like “September 2013” in Google spreadsheet

google sheets

I have a sheet with column A listing the month of the year in human readable format e.g. "December 2013", and column B listing a calculated value of bills issued in that month (from another sheet).
I would like to make the B value use the A value for the date comparisons in a formula by extracting the month and year. But "December 2013" isn't a date… so how can I get a date/year value from this cell to compare against a proper date listed in another cell?

e.g I currently have a formula like:

=sum( filter('Invoices Issued'!J:J ; YEAR('Invoices Issued'!K:K) = 2013 ))

And I would like to replace "2013" with something like YEAR(A4).

Can I do this?

Best Answer

The following formula will help you filter the dates.

Formula

=FILTER(sourceArray, arrayCondition_1,..., arrayCondition_30)
=SUM(FILTER(B2:B, RIGHT(A2:A, 4)="2012", LEFT(A2:A,LEN(A2:A)-5)="february"))

Explained

The first arrayCondition_1 retrieves the year, having 4 digits, taken from the RIGHT. The second arrayCondition_2 retrieves the month (text), based on the total length of the string minus 5 (digits year plus white space). The sourceArray in filtered accordingly and the result is summed up.

Screenshot

enter image description here

Example

I've created an example file for you: FILTER DATE AS TEXT

Related Topic