CODEWITHSUNDEEP
google-sheetsgoogle-sheets-querygoogle-sheets-formula
Orujimaru
Orujimaru

Reputation: 855

ArrayFormula for if Date is greater than Value

I'm trying to create a formula which pulls data from a separate sheet and selects only rows which fulfill a date comparison.

This is what I have at the moment:

=ArrayFormula(if('Complete Staff List'!E2:E < Date(2017;1;1), 'Complete Staff list'!B:C))

So in theory, it should pull all employees who started before 2017.

I understand how ArrayFormula works, but I can't manage to get the 'if' operation to work with it.

Upvotes: 1

Views: 3641

Answers (2)

user4039065
user4039065

Reputation:

If I am interpreting your question correctly, you want to use query, not arrayformula.

In an unused cell with room for results,

=query(B2:E,"select B,C,E where E < date'2017-01-01'")

enter image description here

Dates as criteria in query are particular. See the section on dates in Google sheets query functuion and examples at More query function examples. From a separate sheet, use a named range to make life easy.

Upvotes: 3

Srijan
Srijan

Reputation: 152

It should be like this:

    =ArrayFormula(if('Complete Staff List'!E:E < Date(2017;1;1), 'Complete Staff list'!B:B))

Upvotes: 1

Related Questions