Display most recent past date, and next future date

Copper Contributor

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 named "Recertification Dates" I need to scan cells G2 : BQ2 and display

  • the Most Recent Past Date
  • the Next Future Date

This will be repeated on additional lines beyond line 2.

See attached file, and look at the sheet titled "Desired New Sheet" to see a visual explanation.

 

The Purpose:

I'm wanting to improve a spreadsheet I've made for our Hospice office by cleaning up the display of info and make it more user friendly.  I'd like to only show the start and end dates of the certification period a patient is in.

 

2 Replies

@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

@Sergei Baklan I've downloaded your provided file, and thank you!  However, the min next date does not seem to be working.  I get a #VALUE! result for your formula.