Date Highlight Formula Excel conditional formattin

问题:

I making a leave Planner which highlight the calendar as per the dates using these formulas

=IF(B6="",FALSE,SUMPRODUCT((B6>=INDIRECT("Table1[Start]"))*(B6<=INDIRECT("Table1[End]"))))

=IF(B6="",FALSE,SUMPRODUCT((B6>=INDIRECT("Table1[SD1]"))*(B6<=INDIRECT("Table1[ED1]"))))

I want to apply this formula on same row not on columns how i can give range of Rows or range of cells containing start dates and end dates so same color highlight for one row and other color for other row

回答1:

The formulas would be:

=OR(SUMPRODUCT((B6<>"")*("abc"=INDIRECT("Table1[Names]"))*(B6>=INDIRECT("Table1[Start]"))*(B6<=INDIRECT("Table1[End]"))),SUMPRODUCT((B6<>"")*("abc"=INDIRECT("Table1[Names]"))*(B6>=INDIRECT("Table1[SD1]"))*(B6<=INDIRECT("Table1[ED1]"))),SUMPRODUCT((B6<>"")*("abc"=INDIRECT("Table1[Names]"))*(B6>=INDIRECT("Table1[SD1]"))*(B6<=INDIRECT("Table1[ED1]"))))

As you can see you will need to have a different rule for each name. And change the name.