基于具有日期范围+单个日期的单个单元格设置多个单元格的条件格式 [英] Conditional Formatting multiple cells based on a single cell with date range + singular dates

问题描述

通过条件格式突出显示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))
          )
      )
    )

这篇关于基于具有日期范围+单个日期的单个单元格设置多个单元格的条件格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！