日期突出显示公式Excel条件格式 [英] Date Highlight Formula Excel conditional formatting

问题描述

我做一个休假计划者，使用这些公式，按日期
突出显示日历

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

推荐答案

公式将是：

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

这篇关于日期突出显示公式Excel条件格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！