Conditional Formatting multiple cells based on a single cell with date range + singular dates

Question

Highlighting, via conditional formatting, multiple cells in column G2:Z based on the multiple singular dates in single cells in column C2:C works with =ArrayFormula(OR((SUBSTITUTE(SPLIT($C2, CHAR(10)), ".", "/")*1)=G$1))

But I'm looking for a way to still highlight, via conditional formatting, multiple cells in columns G2:Z but based on single cells in column C2:C that contains date range + singular dates.

sample sheet: https://docs.google.com/spreadsheets/d/1yPUUr24hAwFFcfWKdaknEA3vBmIvDNpscobLbMcHHtU/edit#gid=275757705&range=A1

basic · Accepted Answer · 2021-10-29 13:17:12Z

1

In this case should work:

    =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))
          )
      )
    )

answered Oct 29, 2021 at 13:17

basic

12k2 gold badges15 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Conditional Formatting multiple cells based on a single cell with date range + singular dates

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related