Find the most recent date

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

Find more posts tagged with

Accepted answers

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

All comments

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:

francescabi

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

Paul Newcome

Happy to help. 👍️

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.