Latest Date Before Today

s_mawer

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

Paul Newcome

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.

Paul Newcome

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.

s_mawer

Perfect, that works brilliantly.

Paul Newcome

Excellent. Happy to help! 👍️

PYI

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

Paul Newcome

@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))

AHANSON

For the above question - is it possible to combine first and second requests?

Paul Newcome

@AHANSON Can you explain in more detail?

KatieWermer

@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()))

Paul Newcome

@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.

KatieWermer

@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

Paul Newcome

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?