Latest Date Before Today
Hi
I'm trying to figure out if there is a formula that will return the latest date from a range as long as it is before today. To explain, I have a sheet that has a list of record of previous and planned visit dates per site. I want to be able to find when the last visit to a site was. I've tried using the MAX function, but if there are planned visits already in the sheet it is returning a date in the future. Is there a way of filtering future dates out? Maybe something with an IF function?
Thanks in advance
Best Answer
-
You would need to incorporate a COLLECT function similar to...
=MAX(COLLECT([Date Column]:[Date Column], [Date Column]:[Date Column], @cell <= TODAY()))
This will collect all of the dates that are in the past then return the MAX or the most recent.
Answers
-
You would need to incorporate a COLLECT function similar to...
=MAX(COLLECT([Date Column]:[Date Column], [Date Column]:[Date Column], @cell <= TODAY()))
This will collect all of the dates that are in the past then return the MAX or the most recent.
-
Perfect, that works brilliantly.
-
Excellent. Happy to help! 👍️
-
hi ,,i have two entrys with different dates for same id and is it possible to check what is the last entry Than
k you
-
@PYI If I understand your question correctly, you are also going to want a MAX/COLLECT.
=MAX(COLLECT([Date Column]:[Date Column], [ID Column]:[ID Column], @cell = [ID Column]@row))
-
For the above question - is it possible to combine first and second requests?
-
@AHANSON Can you explain in more detail?
-
@Paul Newcome this information is awesome! Question, I am using the max collect to get the latest date for a project, but the projects run on cycle so they happen over and over, I am looking to see if I can get the last date from the list in the cycle before my next ship date, let me know your thoughts!
almost like this equation but instead of less then today -- i would like to use the data point of my ship date column
=MAX(COLLECT([Date Column]:[Date Column], [Date Column]:[Date Column], @cell <= TODAY()))
-
@KatieWermer Are you able to provide a screenshot for reference? It almost sounds as if you are gong to want to swap out TODAY() with a cell reference such as [Next Ship Date]@row.
-
@Paul Newcome exactly what i am thinking! I tried playing a round a little to see but here is a screenshot
So i need the last date that falls before the pick up date
-
Are you also able to provide a screenshot of the "Fake Data" sheet as well as some examples of what you would expect the output to be?
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!