How to conditional format ONLY the first row that meets a condition?

I have an amortization table with a date next to each row. How do I highlight ONLY the first row that is a larger date than today's date?

Let's say the date column starts at A13. Obviously this...

=A$13>=today()

Would highlight EVERY row that is a date later than today.

I've been racking my brain since yesterday and came up with absurdly complex logic trees that almost achieve what I want, but so complex that I'm certain there MUST be a better way.

Thanks for any help you can offer!

Ben Mora

Posted 2019-11-27T20:37:50.693

Reputation: 103

After posting, I had this idea: =AND($A13>=today(), $A13<today()+31), but in some cases it will highlight 2 rows. Even with +30. +29 sometimes doesn't highlight any. All because each month can be a different number of days. – Ben Mora – 2019-11-27T20:51:04.060

MINIFS would do this if your excel supports that newer function. – Jeeped – 2019-11-27T22:28:03.970

Answers

using A13 as the start of the column:

=COUNTIF($A$13:$A13,">="&today())=1

This will now highlight only the first instance where the date is greater than or equal to today.

Scott Craner

Posted 2019-11-27T20:37:50.693

Reputation: 16 128

That did it! I can't say I know WHY it works, but I guess it does. What is the "&" doing? – Ben Mora – 2019-11-27T23:03:56.150

It concatenates a two parts into one string – Scott Craner – 2019-11-27T23:39:51.300

The newer MINIIFS should be able to do this.

=$a13=minifs($a:$a, $a:$a, “>=”&today())

Jeeped

Posted 2019-11-27T20:37:50.693

Reputation: 2 435