在Google表格上查询和比较日期 [英] Query and compare date on google sheets

问题描述

我正在将sheet1的副本复制到另一个sheet2(相同的文档)上，而query()可以正常工作，直到我要过滤的列为公式单元格(每个都有查询，匹配等的长单元格).

I'm working on making a replica of sheet1 on to another sheet2 (same document), and query() worked fine until the column i want to filter are formula cells (LONG ones each with query, match, etc).

我想做的是在sheet1中的行日期即将到来的工作表中过滤行(有更多的过滤条件，但只是为了简化，这是主要问题).

What i want to do is filter the rows in sheet1 where the event date in column M is upcoming (there are more filter conditions but just to simplify this is the main problem).

我不希望日期过去为空(各种日期格式)或公式给出空字符串"的结果的行.

I don't want the rows where the date is either empty, in the past (various date formats), or where the formula give a result of empty string "".

我尝试过的公式(会出错)-请注意，我只是选择2列进行测试:

= query(sheet1！A3:N，选择I，M，其中I ='新加坡'并且DATEVALUE(M)> TODAY()"，0)

=query(sheet1!A3:N, " select I,M where I = 'Singapore' AND DATEVALUE(M)>TODAY() ",0)

= query(sheet1！A3:N，选择I，M，其中I ='新加坡'并且M> TODAY()"，0)

=query(sheet1!A3:N, " select I,M where I = 'Singapore' AND M>TODAY() ",0)

此公式不会给出错误，但不会显示正确的数据-显示除2017年1月至2017年8月7日以外的所有数据:

= FILTER(sheet1！A3:N，sheet1！I3:I ="Singapore"，sheet1！M3:M> TODAY())

=FILTER(sheet1!A3:N, sheet1!I3:I="Singapore", sheet1!M3:M>TODAY())

此公式给出的输出为空:

= query(sheet1！A3:N，选择I，M，其中I ='新加坡'并且M ='8月22日'"，0)

=query(sheet1!A3:N, " select I,M where I = 'Singapore' AND M='22 August' ",0)

我是Google表格&的新手.应用程序脚本，因此感谢任何人的任何建议 谢谢！

I'm a newbie at google sheets & apps script so appreciate any advice from anyone Thanks!!

推荐答案

查询中没有"today()".

There's no today() in Query .

=query(sheet1!A3:N, " select I,M where I = 'Singapore' AND M > now() ",0)

从理论上讲，只要您具有M格式的所有正确日期(Google表格可以识别(即，公式栏显示正确的日期或时间))，无论实际字符串是什么，这在理论上都是可行的.如果不是，则应手动将所有剩余日期转换为正确的格式.在查询公式中输入的日期的正确格式为date 'yyyy-mm-dd'.日期在表格中的格式没有关系(只要Google表格可以识别出该日期)，但是实际公式中只能包含此格式的日期以进行比较.例如，要查找所有少于2017年8月31日的日期，

This should work theoretically, provided you have all the correct dates in M in any format, which Google sheets recognises (i.e., the formula bar shows the correct date or time) regardless of the actual string. If not, You should manually convert all those remaining dates to correct format. The correct format of date to be entered in the query formula is date 'yyyy-mm-dd'. It doesn't matter what format the date is in sheets ( as long as Google sheets recognises this), but the actual formula must only contain date in this format for comparison. For example , to find all dates less than 31,August, 2017,

=query(A2:B6, "select A where A < date '2017-08-31'")

您可以使用它来找出Google无法识别的所有日期: N:N = M:M * 1 如果在帮助程序列N中出现错误，则不会识别这些日期.即使您没有遇到错误，Google表格也可能会错误地识别日期.

You can use this to figure out all the dates, which Google doesn't recognise: N:N= M:M*1 If you get an error in the helper column N, then those dates are not recognised. Even if you did not get error, it is possible that Google sheets mis-recognizes the date.

编辑 Google表格中日期的语法

EDIT Syntax for date in Google sheets

这篇关于在Google表格上查询和比较日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！