两个日期之间的IF / AND条件格式

时间:2022-12-01 14:02:27

I am trying to implement the following logic but my formulas are not encompassing all possibilities.

我试图实现以下逻辑,但我的公式并不包含所有可能性。

Am I able to accomplish the following using the approach outlined below?

我可以使用下面概述的方法完成以下操作吗?

C5 has a start date(yy/mm/dd), 2013-10-01.
D5 has an end date(yy/mm/dd),2013-10-23.
F3->CX2 has 7 cells merged with the monday's date in the cell.

Example: F3: 30-Sep-13, M3: 07-Oct-13, T3: 14-Oct-13.

实施例:F3:30-Sep-13,M3:07-Oct-13,T3:14-Oct-13。

F4->CX4 and below are the unmerged seven cells, therefore you have seven cells below each week cell.

F4-> CX4及以下是未合并的七个细胞,因此每周细胞下面有七个细胞。

My goal is to use Conditional Formatting when cell has value TRUE to fill the background colour of each individual cell between the start and the end date. However I am having trouble determining the correct formula. I have tried the following in F4 and across and below but none have proven to work for all scenario's:

我的目标是在单元格值为TRUE时使用条件格式来填充开始日期和结束日期之间每个单独单元格的背景颜色。但是我无法确定正确的公式。我已经尝试过F4以及以下版本中的以下内容,但没有一个证明适用于所有场景:

F4=AND(C5=F3, D5>=F3) F5=AND(C5=(F3+1), D5>=(F3+1))...

F4 = AND(C5 = F3,D5> = F3)F5 = AND(C5 =(F3 + 1),D5> =(F3 + 1))...

only fills the cell for the start date not all the cells between the start and end date.

仅填充单元格的开始日期而不是开始日期和结束日期之间的所有单元格。

In short, I need to identify the start cell and fill it, continue filling all cells until the end date.

简而言之,我需要识别起始单元格并填充它,继续填充所有单元格直到结束日期。

Should this be a macro with a while loop?

这应该是一个带有while循环的宏吗?


***I Believe I have solved my question with the following formula:

***我相信我用以下公式解决了我的问题:

=IF(AND((F3)>=$C$5,(F3)<=$D$5),TRUE,FALSE), =IF(AND((F3+1)>=$C$5,(F3+1)<=$D$5),TRUE,FALSE), =IF(AND((F3+2)>=$C$5,(F3+2)<=$D$5),TRUE,FALSE)...Then after 7 cells it becomes: =IF(AND((M3+1)>=$C$5,(M3+1)<=$D$5),TRUE,FALSE), =IF(AND((M3+1)>=$C$5,(M3+1)<=$D$5),TRUE,FALSE)

= IF(AND((F3)> = $ C $ 5,(F3)<= $ D $ 5),TRUE,FALSE),= IF(AND((F3 + 1)> = $ C $ 5,(F3 + 1) <= $ D $ 5),TRUE,FALSE),= IF(AND((F3 + 2)> = $ C $ 5,(F3 + 2)<= $ D $ 5),TRUE,FALSE)...然后在7之后它变为:= IF(AND((M3 + 1)> = $ C $ 5,(M3 + 1)<= $ D $ 5),TRUE,FALSE),= IF(AND((M3 + 1)> = $ C $ 5,(M3 + 1)<= $ D $ 5),TRUE,FALSE)

3 个解决方案

#1


1  

Should this be a macro with a while loop?

这应该是一个带有while循环的宏吗?

It seems it does not have to be, though that depends upon my interpretation of your question, which seems odd if only because the conditions are not in the rows for which they trigger the formatting.

它似乎不一定,但这取决于我对你的问题的解释,这似乎很奇怪,因为条件不在他们触发格式化的行中。

Select the applicable range starting in F4 and HOME > Styles - Conditional Formatting, New Rule..., Use a formula to determine which cells to format and Format values where this formula is true::

从F4和HOME>样式 - 条件格式,新规则...中选择适用的范围,使用公式确定要格式化的单元格和格式化此公式为真的值::

=AND($F$3+COLUMN()-6>=$C5,$F$3+COLUMN()-6<=$D5)

Format..., select choice of formatting, OK, OK.

格式化...,选择格式选择,确定,确定。

两个日期之间的IF / AND条件格式

#2


0  

Here's a stepwise view on the problem as I see it:

这是我看到的问题的逐步观点:

  1. Set up your data and include the actual date of your "weekly view" as part of the sheet:

    设置您的数据并将“每周视图”的实际日期作为工作表的一部分:

    两个日期之间的IF / AND条件格式

  2. Add conditional formatting to a single cell (say, Monday of Week 1) that applies to "a formula to determine which cells to format":

    将条件格式添加到单个单元格(例如,第1周的星期一),该单元格适用于“确定要格式化哪些单元格的公式”:

    两个日期之间的IF / AND条件格式

  3. Copy-and-Paste-formats to the remainder of the cells:

    复制和粘贴格式到剩余的单元格:

    两个日期之间的IF / AND条件格式

    The result should resemble:

    结果应该类似于:

    两个日期之间的IF / AND条件格式

  4. Change the actual cell formatting to "" to remove the date from view:

    将实际单元格格式更改为“”以从视图中删除日期:

    两个日期之间的IF / AND条件格式

The removes the capability to insert any content in the cells (but that wasn't part of the question). If you want to insert content and maintain colouring, you can base the conditional formatting on a similar-sized/shaped layout elsewhere in the sheet and format it accordingly. For example, the following layout provides this yet allows you to enter content in the conditionally formatted area:

删除了在单元格中插入任何内容的功能(但这不是问题的一部分)。如果要插入内容并保持着色,可以将条件格式设置在工作表中其他位置的类似大小/形状的布局上,并相应地对其进行格式化。例如,以下布局提供此功能,允许您在有条件格式的区域中输入内容:

两个日期之间的IF / AND条件格式

#3


0  

Use the formula conditions. I put a start date in A1, and an end date in A2. Modify as needed per your requirements.

使用公式条件。我在A1中设置了开始日期,在A2中设置了结束日期。根据您的要求进行修改。

Then I will apply conditional formatting to values in range E1:E6. Again, modify as per your requirements.

然后我将条件格式应用于E1:E6范围内的值。再次,根据您的要求进行修改。

两个日期之间的IF / AND条件格式

Then simply use Highlight cells Rules > Between

然后只需使用突出显示单元格规则>之间

两个日期之间的IF / AND条件格式

Then, select your start/end values and press OK. Voila!

然后,选择开始/结束值并按OK。瞧!

两个日期之间的IF / AND条件格式

#1


1  

Should this be a macro with a while loop?

这应该是一个带有while循环的宏吗?

It seems it does not have to be, though that depends upon my interpretation of your question, which seems odd if only because the conditions are not in the rows for which they trigger the formatting.

它似乎不一定,但这取决于我对你的问题的解释,这似乎很奇怪,因为条件不在他们触发格式化的行中。

Select the applicable range starting in F4 and HOME > Styles - Conditional Formatting, New Rule..., Use a formula to determine which cells to format and Format values where this formula is true::

从F4和HOME>样式 - 条件格式,新规则...中选择适用的范围,使用公式确定要格式化的单元格和格式化此公式为真的值::

=AND($F$3+COLUMN()-6>=$C5,$F$3+COLUMN()-6<=$D5)

Format..., select choice of formatting, OK, OK.

格式化...,选择格式选择,确定,确定。

两个日期之间的IF / AND条件格式

#2


0  

Here's a stepwise view on the problem as I see it:

这是我看到的问题的逐步观点:

  1. Set up your data and include the actual date of your "weekly view" as part of the sheet:

    设置您的数据并将“每周视图”的实际日期作为工作表的一部分:

    两个日期之间的IF / AND条件格式

  2. Add conditional formatting to a single cell (say, Monday of Week 1) that applies to "a formula to determine which cells to format":

    将条件格式添加到单个单元格(例如,第1周的星期一),该单元格适用于“确定要格式化哪些单元格的公式”:

    两个日期之间的IF / AND条件格式

  3. Copy-and-Paste-formats to the remainder of the cells:

    复制和粘贴格式到剩余的单元格:

    两个日期之间的IF / AND条件格式

    The result should resemble:

    结果应该类似于:

    两个日期之间的IF / AND条件格式

  4. Change the actual cell formatting to "" to remove the date from view:

    将实际单元格格式更改为“”以从视图中删除日期:

    两个日期之间的IF / AND条件格式

The removes the capability to insert any content in the cells (but that wasn't part of the question). If you want to insert content and maintain colouring, you can base the conditional formatting on a similar-sized/shaped layout elsewhere in the sheet and format it accordingly. For example, the following layout provides this yet allows you to enter content in the conditionally formatted area:

删除了在单元格中插入任何内容的功能(但这不是问题的一部分)。如果要插入内容并保持着色,可以将条件格式设置在工作表中其他位置的类似大小/形状的布局上,并相应地对其进行格式化。例如,以下布局提供此功能,允许您在有条件格式的区域中输入内容:

两个日期之间的IF / AND条件格式

#3


0  

Use the formula conditions. I put a start date in A1, and an end date in A2. Modify as needed per your requirements.

使用公式条件。我在A1中设置了开始日期,在A2中设置了结束日期。根据您的要求进行修改。

Then I will apply conditional formatting to values in range E1:E6. Again, modify as per your requirements.

然后我将条件格式应用于E1:E6范围内的值。再次,根据您的要求进行修改。

两个日期之间的IF / AND条件格式

Then simply use Highlight cells Rules > Between

然后只需使用突出显示单元格规则>之间

两个日期之间的IF / AND条件格式

Then, select your start/end values and press OK. Voila!

然后,选择开始/结束值并按OK。瞧!

两个日期之间的IF / AND条件格式