CODEWITHSUNDEEP
excel
David F
David F

Reputation: 41

Conditional formatting highlighted a Date Range

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

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)

Sample

Upvotes: 4

Views: 85

Answers (1)

Dirk Reichel
Dirk Reichel

Reputation: 7979

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

Upvotes: 1

Related Questions