英文:
How do I filter for a date using Developer Mode In Excel without VBA if possible?
问题
这个工作簿正在进行部分搜索。我在处理日期时遇到了问题。我想能够输入MM/YYYY、YYYY或完整日期MM/DD/YYYY。F3:I4是由F3中的筛选函数生成的。
F2、G2和H2上有来自“开发者”选项卡的文本框，并且与其相应的框位置相链接（例如，F2文本框链接到F2）。F3中的公式是：
=FILTER(A1:D6,ISNUMBER(SEARCH(F2,A1:A6))*ISNUMBER(SEARCH(G2,B1:B6)),"未找到匹配项")
英文:
This workbook is doing a partial search. I am having trouble doing the same for Date. I want to be able to either put MM/YYYY or YYYY or full date MM/DD/YYYY. F3:I4 are generated by the filter function in F3.
F2, G2, and H2 have text box from Developer ribbon and are linked to its corresponding box location (e.g. F2 text box is linked to F2).
The Formula in F3 is:
=FILTER(A1:D6,ISNUMBER(SEARCH(F2,A1:A6))*ISNUMBER(SEARCH(G2,B1:B6)),"No Match Found")
答案1
得分: 2
你的日期是否在三个框中：
- 搜索日
- 搜索月份（以数字表示）
- 搜索年份
然后你的公式是：
```markdown
=FILTER(
A1:D6,
(ISNUMBER(SEARCH(F2,A1:A6)))*
(ISNUMBER(SEARCH(G2,B1:B6)))*
(IF(H2<>"",DAY(C1:C6)=H2,TRUE))*
(IF(I2<>"",MONTH(C1:C6)=I2,TRUE))*
(IF(J2<>"",YEAR(C1:C6)=J2,TRUE)),
"未找到匹配项")
<details>
<summary>英文:</summary>
Do you dates in three boxes:
- Search Day
- Search Month(as number)
- Search Year
Then your formula is:
=FILTER(
A1:D6,
(ISNUMBER(SEARCH(F2,A1:A6)))*
(ISNUMBER(SEARCH(G2,B1:B6)))*
(IF(H2<>"",DAY(C1:C6)=H2,TRUE))*
(IF(I2<>"",MONTH(C1:C6)=I2,TRUE))*
(IF(J2<>"",YEAR(C1:C6)=J2,TRUE)),
"No Match Found")
[![enter image description here][1]][1]
[1]: https://i.stack.imgur.com/bVZB7.png
</details>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论