I’ve been struggling to try and figure this one out, it to do with conditional formation in Excel. I want to find a way to highlight the next payment schedule based on the current date. So if today was 10 Feb, the next payment schedule would be 15th Feb and is therefore highlighted. If today was the 16th Feb, A6 and B6 would be highlighted
我一直在努力想弄明白这一点，这和Excel中的条件形成有关。我想找到一种方法来突出显示基于当前日期的下一个付款计划。因此，如果今天是2月10日，下一个付款计划将是2月15日，因此被强调。如果今天是2月16日，A6和B6将会突出显示
Can any one help me with the formula for this, I’m assuming its conditional formatting with a formula. but the formula stumps me. this formula would need to be applied to a large range of dates (600 dates)
有谁能帮我解这个公式吗，我假设它的条件格式是一个公式。但这个公式难倒了我。这个公式需要应用于大量的日期(600个日期)
1 个解决方案
#1
1
Simply try this formula:
只是试试这个公式:
=COUNTIFS($A$2:$A$9,"<="&$A2,$A$2:$A$9,">"&TODAY())=1
if you want to show also a day which is the actual day then simply add the = to the today part:
如果你也想显示一天，也就是实际的一天，那么只需在今天部分添加=:
=COUNTIFS($A$2:$A$9,"<="&$A2,$A$2:$A$9,">="&TODAY())=1
but make sure to not miss any $ (or do to much of them) ;)
但是要确保不要漏掉任何一分钱(或者对其中的大部分做些什么);
alternatively you also could use the short formula (which may be slower for bigger tables:
或者你也可以使用简短的公式(对于较大的表来说可能会比较慢:
=$A2=MIN(IF($A$2:$A$9>TODAY(),$A$2:$A$9))
$A$2:$A$9>=TODAY() will include the actual day
$2:$ 9>=今天()将包括实际的一天。
#1
1
Simply try this formula:
只是试试这个公式:
=COUNTIFS($A$2:$A$9,"<="&$A2,$A$2:$A$9,">"&TODAY())=1
if you want to show also a day which is the actual day then simply add the = to the today part:
如果你也想显示一天，也就是实际的一天，那么只需在今天部分添加=:
=COUNTIFS($A$2:$A$9,"<="&$A2,$A$2:$A$9,">="&TODAY())=1
but make sure to not miss any $ (or do to much of them) ;)
但是要确保不要漏掉任何一分钱(或者对其中的大部分做些什么);
alternatively you also could use the short formula (which may be slower for bigger tables:
或者你也可以使用简短的公式(对于较大的表来说可能会比较慢:
=$A2=MIN(IF($A$2:$A$9>TODAY(),$A$2:$A$9))
$A$2:$A$9>=TODAY() will include the actual day
$2:$ 9>=今天()将包括实际的一天。