Forum Discussion
Display most recent past date, and next future date
How can I reference a row on another sheet in the same document and display the most recent past date in one cell, and the next future date in that row in another cell? On a different sheet sheet ...
breinicke , that could be
max before
=LOOKUP(2,1/('Recertification Dates'!$G2:$BQ2<TODAY()),'Recertification Dates'!$G2:$BQ2)
min after
=AGGREGATE(15,6,1/('Recertification Dates'!$G2:$BQ2>TODAY())*'Recertification Dates'!$G2:$BQ2,1)
these are in new Sheet1 attached. Put formulas in right place and drag them down fro next rows.
If you are Office 365 subscriber or on Excel 2019 you may use MINIFS and MAXIFS