Google Sheets – Query or Filter Data Using Date Time Format

google sheetsgoogle-sheets-datesgoogle-sheets-query

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…

Best Answer

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

Output

Manual paste

Indeed, pasting plain text with Ctrl-V results in it having the default size of 10. It seems that pasting is understood to overwrite the formatting of the cell, even if it's plain text coming in.

The solution is to paste using Ctrl-Shift-V, which means paste values only. Then the font size is 12 pt, as set in the spreadsheet, regardless of where the text came.

Script modification

Generally, when you simply use .setValue(value), the existing formatting of the cell remains. For example, if the cell A1 was formatted as "1 Jun 2015" then executing

sheet.getRange("A1").setValue(new Date());

will respect that formatting.

The format is changed only if the script explicitly applies a different formatting with Utilities.formatDate(). I would say this is to be expected, and the fact that the old version behaved differently is more of a bug or poor design.

It's known that the new sheets have a somewhat different format functionality.

Migration can be a breaking change for some scripts: for example, one of my scripts stopped working because it tried to store more than 50000 characters in a single cell (which was possible with the old version, but not with the new one).

Google Sheets – Combine Text from Multiple Cells into Single Cell

if you need it everything in one cell, try this:

=JOIN(CHAR(10); FILTER(ARRAYFORMULA(Sheet1!B1:B &" "&
                                    Sheet1!C1:C &" "&
                                    Sheet1!D1:D); Sheet1!A1:A=G3))

blank error fix:

=IF(G3<>""; JOIN(CHAR(10); FILTER(ARRAYFORMULA(Sheet1!B1:B &" "&
                                               Sheet1!C1:C &" "&
                                               Sheet1!D1:D); Sheet1!A1:A=G3));)

Best Answer

Related Solutions

Google-sheets – Cell formatting in Google Spreadsheets does not get preserved

Manual paste

Script modification

Google Sheets – Combine Text from Multiple Cells into Single Cell

Related Topic