基于另一个单元格中日期的条件格式Excel [英] Conditional Formatting Excel Based On Date In Another Cell

问题描述

我仔细检查了所有重新格式化的内容，但没有发现任何问题。

I looked through everything re conditional formatting, but nothing touched on my issue.

我试图根据另一个单元格中的日期突出显示列中的单元格，以及相关单元格中的文字。我已经能够分别将这两个部分作为基本的条件格式进行处理，但是不确定如何使用公式将它们全部作为一个工作。

I am trying to highlight cells in a column based on the date in another cell, as well as the text in the cell in question. I have been able to do both parts separately as basic conditional formatting, but not sure how to have it all work as one using a formula.

A1 有文本，而 D1 有日期。如果日期是今天，并且文本是2或3，我希望使用条件格式为单元格着色。

A1 has the text and D1 has the date. If the date is today and the text is either 2 or 3, I want the cell to be coloured using conditional formatting.

这是我尝试用于条件格式的公式，但没有结果：-

Here is the formula I tried for conditional formatting, but with no result:-

=IF(AND($D1=TODAY(), OR(A1="3", A1="2")))

基本上，如果 D1 中的日期是今天，而 A1 是2或3，然后应用条件格式。看起来很简单，但我只能将其作为单独的部分工作

Basically, if the date in D1 is today and A1 is either a 2 or 3, then apply the conditional formatting. Seems simple, but I can only get it working as separate parts

预先感谢

推荐答案

只是想象条件格式的

• Excel中的每个日期实际上都是一个数字，例如对于 27.01.2019 是
  43492 。您可以将包含日期的单元格设置为数字格式，然后自己查看
  。


• 当有时间显示日期时，还有一个
  小数部分，例如对于 27.01.2019 14:52:17 ，该数字将为
  约 43492.6196 ，与 43492 。



Just 'Imagine' IF in Conditional Formatting

• Every date in Excel is actually a number, e.g. for 27.01.2019 it is 43492. You can format a cell containing a date, as number, and see for yourself.
• When there is time with a date, there is additionally a decimal part, e.g. for 27.01.2019 14:52:17 the number would be approximately 43492.6196, which is different than 43492.

因此，您必须使用 INT 函数将数字四舍五入到最接近的整数，从而产生以下条件格式公式：

Therefore you have to use the INT function to round the number down to the nearest integer resulting in the following conditional formatting formula:




=AND(INT(D1)=TODAY(),OR(A1="FA_Win_3",A1="FA_Win_2"))




使用例如对于单元格 E1 。



• 因此，公式背后的逻辑可以解释为：（试想一下
  IF ）： IF 单元格 D1的四舍五入值等于
  今天的价值的值（日期）和的价值单元格 A1 中的 Ether FA_Win_3 OR FA_Win_2 ， DO 应用格式（否则不要）


• So the logic behind the formula could be interpreted: (just imagine the IF): IF the rounded down value of cell D1 is equal to TODAY's value (date) AND the value in cell A1 is EITHER FA_Win_3 OR FA_Win_2, DO apply the formatting (, or else don't).

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