根据另一列中的日期更改一个Google表格列的背景 [英] Change the background of one google sheets column based on the date in another column

问题描述

我已经看到了许多其他条件下的答案。不过，我似乎无法修改任何这些日期范围的工作。我想要的是列E的列颜色发生变化，如果列H中的日期在今天和今天5天之间。

我试过不同版本的这个公式：H3：H150 =今天（）+5没有成功

这并不给我现在和5天之间的范围，但我可以做像这样的多条规则，只是每一个（=今天（）+4，=今天（）+3等），但显然我需要这个规则首先工作。

谢谢！

解决方案

条件格式比看起来更有用期待。清除要格式化的列的格式，选择格式和格式，条件格式...，格式化单元格，如果... 自定义公式和：

  =和（H1> today（），H1 ）
  

然后选择填充选项和完成。

em> next 5天（改变过去过去五天）的角括号）。 选择要格式化的列（不是包含日期的列 - H ）。如果仅适用于Rows3：150（并且通常对于处理速度来说，限制应用CF的范围是一个好主意），那么通过选择要格式化的列中的行3：150来开始（而不是整个列）或调整规则的应用到范围，但无论在哪种情况下都使用 H3 来代替 H1 。

CF应自动应用写入所选范围第一行的规则，然后调整它的第二行和后续行的方式复制下来将调整公式（如果有的话）是否在表中的单元格中。

I've seen lots of answers for various other conditions under which to do this. However I can't seem to modify any of these to work for a date range. What I want is for the column color of column E to change if the date in column H is between today and 5 days from today.

I've tried varying versions of this formula: H3:H150 =today() +5 with no success

This doesn't give me the range of between now and 5 days from now but I could do multiple rules like this and just go down on each one(=today() +4, =today() +3, etc) but obviously I need this rule to work first.

Thanks!

解决方案

Conditional formatting is more helpful than you seem to be expecting. Clear formatting from the column to be formatted, select it and Format, Conditional formatting..., Format cells if... Custom formula is and:

=and(H1>today(),H1<today()+6,H1<>"")

Then select fill of choice and Done.

This should format the next five days (change the angled brackets around for the past five days).

Setting the range is achieved with selection of the column to be formatted (not the one with dates in it - H). If only to apply to Rows3:150 (and in general it is a good idea, for speed of processing, to restrict the ranges to which CF is applied) then either start by selecting Rows3:150 in the column to be formatted (instead of the entire column) or adjust the Apply to range for the rule, but in either case use H3 in place of H1 in the formula above.

CF should automatically apply the rule as written to the first row in the selected range and then adjust it for the second and subsequent rows in the way copying down would adjust the formula (if at all) were it in a cell in the sheet.

这篇关于根据另一列中的日期更改一个Google表格列的背景的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！