2

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?

1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.