英文:
How do i resolve this date filter issue in a google sheets query?
问题
第B列显示的是标准日期格式。这个查询用于生成我的多个医疗类别支出的表格。
B314单元格中的日期是1/1/2021，C314单元格中输入的是12/31/2021。我只想显示2021年的交易。这个日期筛选器不起作用，因为它继续显示来自2022年和2023年的交易。
=QUERY(Transactions!B1:Transactions!F8094, "选择 B,C,D,E 其中 B >= 日期 '"&TEXT(B314,"yyyy-mm-dd")&"' 并且 B <= 日期 '"&TEXT(C314,"yyyy-mm-dd")&"' 并且 D= '个人 - 医疗费用' 或 D='个人 - 旅行' 或 D='个人 - 医疗保险' 按 B 排序",1)
当我删除日期单元格引用并输入完整表达式时，同样的情况发生：
=QUERY(Transactions!B1:Transactions!F8094, "选择 B,C,D,E 其中 B >= 日期 '"&TEXT(DATEVALUE("1/1/2021"),"yyyy-mm-dd")&"' 并且 B <= 日期 '"&TEXT(DATEVALUE("12/31/2021"),"yyyy-mm-dd")&"' 并且 D= '个人 - 医疗费用' 或 D='个人 - 旅行' 或 D='个人 - 医疗保险' 按 B 排序",1)
我尝试了许多小的更改，但无法使其工作。我是一个新手，对查询不太熟悉。
英文:
Column B on my Transactions Tab shows Dates in standard date format.
This query works to produce a table of my expenses for several medical categories.
Cell B314 has the date 1/1/2021, Cell C314 has 12/31/2021 entered. I just want to show transactions for 2021. This date filter dies not work as it continues to show transactions from 2022 and 2023
=QUERY(Transactions!B1:Transactions!F8094,"Select B,C,D,E Where B >= date '"&TEXT(B314,"yyyy-mm-dd")&"' AND B <= date '"&TEXT(C314,"yyyy-mm-dd")&"' AND D= 'Personal - Medical Expense' or D='Personal - Travel' or D='Personal - Medical Insurance' ORDER BY B",1)
The same things happens when I remove the date cell references and enter the full expression:
=QUERY(Transactions!B1:Transactions!F8094,"Select B,C,D,E where B >= date '"&TEXT(DATEVALUE("1/1/2021"),"yyyy-mm-dd")&"' AND B <= date '"&TEXT(DATEVALUE("12/31/2021"),"yyyy-mm-dd")&"' AND D= 'Personal - Medical Expense' or D='Personal - Travel' or D='Personal - Medical Insurance' ORDER BY B",1)
I tried many small changes but cannot get it to work. Im a rookie with queries.
答案1
得分: 0
我认为您的问题出现在OR语句中。尝试使用以下查询：
=QUERY(Transactions!B1:F8094,"Select B,C,D,E Where B >= date '"&TEXT(B314,"yyyy-mm-dd") &
"' AND B <= date '"&TEXT(C314,"yyyy-mm-dd")&
"' AND (D= 'Personal - Medical Expense' or D='Personal - Travel' or D='Personal - Medical Insurance') ORDER BY B",1)
英文:
I assume your problem in OR statement. Give a try on below query.
=QUERY(Transactions!B1:F8094,"Select B,C,D,E Where B >= date '"&TEXT(B314,"yyyy-mm-dd") &
"' AND B <= date '"&TEXT(C314,"yyyy-mm-dd")&
"' AND (D= 'Personal - Medical Expense' or D='Personal - Travel' or D='Personal - Medical Insurance') ORDER BY B",1)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论