Trying to set up a bit of a to do list with conditional formatting dates. Any time I open the file B2 will load =today()'s date. What I'm hoping to do is find a formula that will allow something like if C2 < B2 (Over due) then conditional formatting rule, or if C2 is 7 days more than B2 then format rule 2.
尝试使用条件格式化日期设置一些待办事项列表。每次打开文件B2都会加载= today()的日期。我希望做的是找到一个公式,允许类似C2
Not sure how to proceed with this as =today() isn't being treated like numeric data.
不确定如何继续这样做= today()不被视为数字数据。
Any suggestions?
Thanks!
1 个解决方案
#1
To apply to C2, select it and HOME > Styles - Conditional Formatting, New Rule..., Use a formula to determine which cells to format and Format values where this formula is true::
要应用于C2,请选择它,然后选择HOME和样式 - 条件格式,新规则...,使用公式确定要格式化的单元格以及格式化此公式为真的值::
=C2<B2
Format..., select choice of formatting, OK, OK.
格式化...,选择格式选择,确定,确定。
Then repeat with:
然后重复:
=C2=B2+7
and (presumably) a different choice of formatting, though could be the same (in which case a single rule would be sufficient).
和(推测)不同的格式选择,虽然可以是相同的(在这种情况下,单个规则就足够了)。
Beware if C2 contains time as well as date the second rule above will not work (and not that formatting will only work for 7 days time, not 6 nor 8 ahead etc, only seventh day from today not counting today).
请注意,如果C2包含时间和日期,则上述第二条规则将不起作用(并不是格式化只能工作7天,而不是6或8提前等,只有今天的第七天不计算今天)。
TODAY() could be part of the formulae (if adjusted) - does not need to bein a separate cell.
TODAY()可以是公式的一部分(如果调整) - 不需要在单独的单元格中。
To apply to ore than one cell (eg an entire column) is quite possible with slight adjustments.
通过轻微调整,很可能应用于矿石而不是一个细胞(例如整个柱)。
#1
To apply to C2, select it and HOME > Styles - Conditional Formatting, New Rule..., Use a formula to determine which cells to format and Format values where this formula is true::
要应用于C2,请选择它,然后选择HOME和样式 - 条件格式,新规则...,使用公式确定要格式化的单元格以及格式化此公式为真的值::
=C2<B2
Format..., select choice of formatting, OK, OK.
格式化...,选择格式选择,确定,确定。
Then repeat with:
然后重复:
=C2=B2+7
and (presumably) a different choice of formatting, though could be the same (in which case a single rule would be sufficient).
和(推测)不同的格式选择,虽然可以是相同的(在这种情况下,单个规则就足够了)。
Beware if C2 contains time as well as date the second rule above will not work (and not that formatting will only work for 7 days time, not 6 nor 8 ahead etc, only seventh day from today not counting today).
请注意,如果C2包含时间和日期,则上述第二条规则将不起作用(并不是格式化只能工作7天,而不是6或8提前等,只有今天的第七天不计算今天)。
TODAY() could be part of the formulae (if adjusted) - does not need to bein a separate cell.
TODAY()可以是公式的一部分(如果调整) - 不需要在单独的单元格中。
To apply to ore than one cell (eg an entire column) is quite possible with slight adjustments.
通过轻微调整,很可能应用于矿石而不是一个细胞(例如整个柱)。