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.

1 Answer 1

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

1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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