5

I have a set of data in sheets, and the first column is an automated date added record. I would then like to filter this data set on a number of criteria including whether the date added matches a date input into a certain cell, in this case A2.

The date added record is in the format 10/03/2016 19:25:45.

The filter I am currently trying to use is

=FILTER(
  'Output Checker'!A:P,
  'Output Checker'!A:A=contains(A2),
  'Output Checker'!L:L <2, 
  'Output Checker'!M:M ="NO",
  'Output Checker'!N:N ="Yes", 
  'Output Checker'!O:O ="NO"
) 

where A2 = 10/03/2016

but this keeps throwing up a

N/A No matches are found in FILTER evaluation.

4

Short answer

Try

=FILTER(
  'Output Checker'!A:P,
  ROUNDDOWN('Output Checker'!A:A)=A2,
  'Output Checker'!L:L <2, 
  'Output Checker'!M:M ="NO",
  'Output Checker'!N:N ="Yes", 
  'Output Checker'!O:O ="NO"
) 

Explanation

Google Sheets doesn't have a built-in function called CONTAINS. To evaluate that a date-time value met a date only criteria, round down the date value. This works because Google Sheets uses serialized numbers for date-times where, days are whole numbers and hours are fractions of a day (1 hour = 1 / 24 of a day).

  • The way that thanks is said in this site is upvoting a helpful questions and answers, by the other side as the author of a question you could mark an answer as accepted. For further details, please take a look to What should I do when someone answers my question? – Rubén Oct 4 '16 at 11:20

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.