问题描述
通过条件格式突出显示G2:Z列中的多个单元格基于C2:C列中单个单元格中的多个单元格=ArrayFormula(OR((SUBSTITUTE(SPLIT($C2, CHAR(10)), ".", "/")*1)=G$1))
但我正在寻找一种方法,通过条件格式,仍然突出显示列G2:Z中的多个单元格,但基于列C2:C中包含日期范围+单个日期的单个单元格。
样表:https://docs.google.com/spreadsheets/d/1yPUUr24hAwFFcfWKdaknEA3vBmIvDNpscobLbMcHHtU/edit#gid=275757705&range=A1
推荐答案
在这种情况下应该可以工作:
=ArrayFormula(
OR(
(G$1=SUBSTITUTE(SPLIT(TRANSPOSE(SPLIT($C2,CHAR(10)))," - "),".","/")*1)+
(G$1>=INDEX(SUBSTITUTE(SPLIT(TRANSPOSE(SPLIT($C2,CHAR(10)))," - "),".","/")*1,0,1))*
(G$1<=IFERROR
(
INDEX(SUBSTITUTE(SPLIT(TRANSPOSE(SPLIT($C2,CHAR(10)))," - "),".","/")*1,0,2),
INDEX(SUBSTITUTE(SPLIT(TRANSPOSE(SPLIT($C2,CHAR(10)))," - "),".","/")*1,0,1))
)
)
)