Find the most recent date

francescabi

Hi All,

I have site 001. Below Site 001 there are children. Each child is marked as site 001 in the Site column. Some of the rows have a Pharmacy Visit Date, some don't. Visits dates go into chronological order, from older to newer. I need to populate a column called T logs range start. The date to display is the previous pharmacy visit date for site 001. For example, visit#3 was on 01/06/25, visit #5 was on 05/06/25 (visit #4 doesn't have a date as it wasn't a pharmacy visit but a site visit instead) I want the formula to display '01/06/25' in the row of visit #5. I have used =MAX(COLLECT([Pharmacy Visit Date]:[Pharmacy Visit Date], Site:Site, @cell = "001"))

But it is displaying the visit on 05/06/25, ot the previous one. Can you please help?

Thanks a lot!

Francesca

Paul Newcome

Try something like this:

=IF([Pharmacy Visit?]@row = "Yes", MAX(COLLECT([Pharmacy Visit Date]:[Pharmacy Visit Date], [Pharmacy Visit Date]:[Pharmacy Visit Date], @cell < [Pharmacy Visit Date]@row, Site:Site, @cell = Site@row)))

Paul Newcome

Are you able to provide some screenshots for context?

francescabi

Screenshot 2025-06-25 083850.png

Here a screenshot. I need to populate T logs range start on row corresponding t visit #5, and it should diaply 01-Jun-25 as it was the previous pharmacy visit date

Paul Newcome

Try something like this:

=IF([Pharmacy Visit?]@row = "Yes", MAX(COLLECT([Pharmacy Visit Date]:[Pharmacy Visit Date], [Pharmacy Visit Date]:[Pharmacy Visit Date], @cell < [Pharmacy Visit Date]@row, Site:Site, @cell = Site@row)))

francescabi

That worked, thank you so much!!!!!!!

Paul Newcome

Happy to help. 👍️