英文:
Testing if one date range is partially included in a second date range
问题
| Range A | |
|---|---|
| 1 Jan 2023 | 31 Mar 2023 |
| 测试范围 | 期望结果: | |
|---|---|---|
| 1 Feb 2023 | 15 Feb 2023 | TRUE |
| 15 Mar 2023 | 30 Jun 2023 | TRUE |
| 15 Dec 2022 | 15 Jan 2023 | TRUE |
| 1 Jun 2023 | 30 Jun 2023 | FALSE |
英文:
| Range A | |
|---|---|
| 1 Jan 2023 | 31 Mar 2023 |
| Test Ranges | Expected Result: | |
|---|---|---|
| 1 Feb 2023 | 15 Feb 2023 | TRUE |
| 15 Mar 2023 | 30 Jun 2023 | TRUE |
| 15 Dec 2022 | 15 Jan 2023 | TRUE |
| 1 Jun 2023 | 30 Jun 2023 | FALSE |
I would like to know if any date in a test range is inside of a second date range (Range A)
Here is a test spreadsheet:
https://docs.google.com/spreadsheets/d/1WO9PFeJ8YORmlE5GDcwiKiK-pbmak8Vw1EIurP7H32E/edit#gid=0
答案1
得分: 1
以下是翻译好的内容：
尝试：
使用简单的AND() OR()条件。尝试以下内容：
=OR(AND(A4>=$A$2,A4<=$B$2),AND(B4>=$A$2,B4<=$B$2))
然而，这在arrayformula中不起作用，所以您必须手动拖动它到您的范围。
使用相同逻辑的另一个公式：
=ARRAYFORMULA(IF((A4:A8>=$A$2)*(A4:A8<=$B$2)+(B4:B8>=$A$2)*(B4:B8<=$B$2)>0,"TRUE","FALSE"))
结果：
参考资料：
英文:
Try:
Using simple AND() OR() conditions. Try the following:
=OR(AND(A4>=$A$2,A4<=$B$2),AND(B4>=$A$2,B4<=$B$2))
However, this does not work with arrayformula so you have to drag it down manually to your range.
Another formula using the same logic:
=ARRAYFORMULA(IF((A4:A8>=$A$2)*(A4:A8<=$B$2)+(B4:B8>=$A$2)*(B4:B8<=$B$2)>0,"TRUE","FALSE"))
Result:
References:
答案2
得分: 0
Please use the following formula
=MAP(B33:B41,C33:C41,
LAMBDA(bbb,ccc,IF(bbb="", "",
IF(AND(OR($B$31<=bbb,$B$31<=ccc),OR(ccc<=$C$31,bbb<=$C$31))=TRUE,TRUE,FALSE)))))
(Do adjust the formula according to your ranges and locale)
英文:
Please use the following formula
=MAP(B33:B41,C33:C41,
LAMBDA(bbb,ccc,IF(bbb="","",
IF(AND(OR($B$31<=bbb,$B$31<=ccc),OR(ccc<=$C$31,bbb<=$C$31))=TRUE,TRUE,FALSE))))
(Do adjust the formula according to your ranges and locale)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论