Microsoft Excel:有条件格式化日期(在特定范围内) [英] Microsoft Excel: Conditional Formatting of Dates (Within Certain Ranges)
问题描述
尝试在excel中执行条件格式化,对我的数据来说似乎有点棘手。
我有654个日期。 654行/条目。
我需要使用三种颜色:
绿色 - 使细胞绿色在今天10个月内DATE
黄色 - 使单元格黄色在今天的10至12个月内$ /
RED - Make单元格红色,从今天的日期起超过12个月,或者在单元格中没有任何数据
我知道这是不正确的,但我觉得我是在正确的轨道上:
red =($ G $ 428:$ G $ 655< = TODAY()+ 365)
如何在这个公式中容纳空白单元格?
yellow =($ G $ 3:$ G $ 427 < = TODAY()+ 300〜365)
您如何正确指定10到12个月之间?
green =($ G $ 3:$ G $ 427< = TODAY()+ 300)
任何输入都是赞赏的。
我的测试范围是B3:B35(在应用规则之前选择)
= ABS(B3-TODAY())< = 300'< 10个月
= OR(B3 =,ABS -TODAY())> 365)'超过1年或空
= ABS((B3-TODAY())> 300)'> 10个月(上述规则负责> ; 12 mo)
Trying to do conditional formatting in excel and it seems a little tricky for my data.
I have 654 dates. 654 rows/entries.
I need to use three colors:
Green - Make the cells green that are within 10 MONTHS OF TODAY'S DATE
Yellow - Make the cells yellow that are within 10 TO 12 MONTHS OF TODAY'S DATE
RED - Make the cells red that more than 12 MONTHS away from today's date OR do not have any data in the cell
I know this isn't correct but I feel like I'm on the right track:
red=($G$428:$G$655<=TODAY() + 365)
how would I accommodate blank cells in this formula?
yellow=($G$3:$G$427<=TODAY() + 300 to 365)
How would you correctly specify 'between 10 and 12 months'?
green=($G$3:$G$427<=TODAY() + 300)
Any input is appreciated.
My test range was B3:B35 (selected before applying the rules)
=ABS(B3-TODAY())<=300 ' <10 months
=OR(B3="",ABS(B3-TODAY())>365) 'more than 1 year or empty
=ABS((B3-TODAY())>300) ' >10 months (rule above takes care of >12 mo)
这篇关于Microsoft Excel:有条件格式化日期(在特定范围内)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!