0

Example sheet.

Sheet2

In cell A2 I have:

=QUERY(Sheet1!A1:Q, "Select * where B >= datetime '"&TEXT($A$1,"MM-DD-YYYY HH:mm:ss")&"'")

In cell A1 I have:

6/17/2020 10:53:22 AM

Sheet1:
Column A (user) and B (date and time)

A       B
-----------------------------
User1   6/17/2020 10:53:22 AM
User2   6/17/2020 10:53:22 AM
User3   6/17/2020 11:10:55 AM
User4   6/17/2020 11:33:57 AM
User5   6/24/2020 10:47:34 AM
User6   6/25/2020 4:50:39 AM
User7   6/24/2020 4:00:24 AM
User8   7/4/2020 5:44:25 AM
User9   7/4/2020 7:08:29 AM
User10  7/4/2020 8:49:24 AM
User11  7/3/2020 7:36:46 AM

The expected output in A2 Sheet2 is:

User1   6/17/2020 10:53:22 AM
User2   6/17/2020 10:53:22 AM

but the query I used outputs all of the data from Sheet 1.

Hope someone can help me...

  • Welcome. The expected output in A2 Sheet2 is User 1 and User 2. Why is the output only these two? Every user's timestamp is greater than "6/17/2020 10:53:22 AM". Seems right to me that the results would be every user. Are you sure that you're not looking for "<=", or just "="?? – Tedinoz Jul 4 at 15:25
  • My bad. Cell A1 should have: User11 7/3/2020 7:36:46 AM and should show User8 7/4/2020 5:44:25 AM User9 7/4/2020 7:08:29 AM User10 7/4/2020 8:49:24 AM User11 7/3/2020 7:36:46 AM – KB John Jul 5 at 3:40
0

You have UserID and date data in two columns. The date is expressed as "mm/dd/yyyy hh:mm:ss AM/PM". You have a criterion cell for the date and time in the same format as the reference data. Your query output is all of the data rather than just those rows that satisfy the date criteria.

The reason that your query isn't working is because the string literal for date, datetime and timestamp does NOT include the AM/PM suffix Doc ref. When the suffix is removed from the data, the query works as expected.

This is the query:

=query(revised_data!A2:B12,"select A, B where B >= datetime '"&TEXT(A1,"yyyy-mm-dd HH:mm:ss")&"'")


Raw Data

Input data

Output

Output

| improve this answer | |
  • Actually, it worked with AM and PM. Thank you so much! – KB John Jul 9 at 14:01

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.