Home

Display most recent past date, and next future date

%3CLINGO-SUB%20id%3D%22lingo-sub-418049%22%20slang%3D%22en-US%22%3EDisplay%20most%20recent%20past%20date%2C%20and%20next%20future%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-418049%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSTRONG%3EHow%20can%20I%20reference%20a%20row%20on%20another%20sheet%20in%20the%20same%20document%20and%20display%20the%20most%20recent%20past%20date%20in%20one%20cell%2C%20and%20the%20next%20future%20date%20in%20that%20row%20in%20another%20cell%3F%26nbsp%3B%26nbsp%3B%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EOn%20a%20different%20sheet%20sheet%20named%20%22Recertification%20Dates%22%20I%20need%20to%20scan%20cells%20G2%20%3A%20BQ2%20and%20display%3C%2FP%3E%3CUL%3E%3CLI%3Ethe%20Most%20Recent%20Past%20Date%3C%2FLI%3E%3CLI%3Ethe%20Next%20Future%20Date%3C%2FLI%3E%3C%2FUL%3E%3CP%3EThis%20will%20be%20repeated%20on%20additional%20lines%20beyond%20line%202.%3C%2FP%3E%3CP%3ESee%20attached%20file%2C%20and%20look%20at%20the%20sheet%20titled%20%22Desired%20New%20Sheet%22%20to%20see%20a%20visual%20explanation.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CEM%3EThe%20Purpose%3A%3C%2FEM%3E%3C%2FP%3E%3CP%3E%3CEM%3EI'm%20wanting%20to%20improve%20a%20spreadsheet%20I've%20made%20for%20our%20Hospice%20office%20by%20cleaning%20up%20the%20display%20of%20info%20and%20make%20it%20more%20user%20friendly.%26nbsp%3B%20I'd%20like%20to%20only%20show%26nbsp%3Bthe%20start%20and%20end%20dates%20of%20the%20certification%20period%20a%20patient%20is%20in.%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-418049%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-418532%22%20slang%3D%22en-US%22%3ERe%3A%20Display%20most%20recent%20past%20date%2C%20and%20next%20future%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-418532%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F317462%22%20target%3D%22_blank%22%3E%40breinicke%3C%2FA%3E%26nbsp%3B%2C%20that%20could%20be%3C%2FP%3E%0A%3CP%3Emax%20before%3C%2FP%3E%0A%3CPRE%3E%3DLOOKUP(2%2C1%2F('Recertification%20Dates'!%24G2%3A%24BQ2%26lt%3BTODAY())%2C'Recertification%20Dates'!%24G2%3A%24BQ2)%3C%2FPRE%3E%0A%3CP%3Emin%20after%3C%2FP%3E%0A%3CPRE%3E%3DAGGREGATE(15%2C6%2C1%2F('Recertification%20Dates'!%24G2%3A%24BQ2%26gt%3BTODAY())*'Recertification%20Dates'!%24G2%3A%24BQ2%2C1)%3C%2FPRE%3E%0A%3CP%3Ethese%20are%20in%20new%20Sheet1%20attached.%20Put%20formulas%20in%20right%20place%20and%20drag%20them%20down%20fro%20next%20rows.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20are%20Office%20365%20subscriber%20or%20on%20Excel%202019%20you%20may%20use%20MINIFS%20and%20MAXIFS%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-482999%22%20slang%3D%22en-US%22%3ERe%3A%20Display%20most%20recent%20past%20date%2C%20and%20next%20future%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-482999%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BI've%20downloaded%20your%20provided%20file%2C%20and%20thank%20you!%26nbsp%3B%20However%2C%20the%20min%20next%20date%20does%20not%20seem%20to%20be%20working.%26nbsp%3B%20I%20get%20a%20%23VALUE!%20result%20for%20your%20formula.%3C%2FP%3E%3C%2FLINGO-BODY%3E
breinicke
New 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.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies