MAX DATE FROM 3 COLUMNS

Hi,

On a separate sheet I would like to capture the max (or last) date from another sheet. As shown below, I would like to log the latest "DATE OF VISIT" from the 3 treatment columns. In this example the correct date would be 9/06/23. I've tried multiple versions of MAX(COLLECT but am only getting the first date, not the latest.

Thanks for your help in advance.

Sam

Find more posts tagged with

Accepted answers

Eric Law

@Sam Lugiano What formula are you using? Does it matter which columns are checked, or if at least 1 is checked? I would recommend a helper column on this sheet that helps with the find. Name it Helper Date, or whatever you want. Set the formula to be

=IFERROR(IF(COUNTIF([PAIN MGT INIT]@row:[PAIN MGT D/C]@row, 1)>0, [DATE OF VISIT]@row),"")

Then where you are trying to grab the info you will use

=MAX([HELPER DATE]:[HELPER DATE])

All comments

Eric Law

=IFERROR(IF(COUNTIF([PAIN MGT INIT]@row:[PAIN MGT D/C]@row, 1)>0, [DATE OF VISIT]@row),"")

Then where you are trying to grab the info you will use

=MAX([HELPER DATE]:[HELPER DATE])

Sam Lugiano

Thank you Eric. I got the correct date using a helper column. I just wanted to know if there was a formula that would allow me to avoid a helper column. This will have to be done across many sheets so I was trying to see if there was a shortcut. This was the formula I was using:

=INDEX(MAX(COLLECT({Patient Visit Log - DOV}, {Patient Visit Log - DOV}, ISDATE(@cell), {Patient Visit Log - Barcode}, BARCODE@row, {Patient Visit & Treatment Log Pain Mgt Init}, 1)), 1, INDEX(MAX(COLLECT({Patient Visit Log - DOV}, {Patient Visit Log - DOV}, ISDATE(@cell), {Patient Visit Log - Barcode}, BARCODE@row, {Patient Visit & Treatment Log Pain Mgt F/U}, 1)), 1, INDEX(MAX(COLLECT({Patient Visit Log - DOV}, {Patient Visit Log - DOV}, ISDATE(@cell), {Patient Visit Log - Barcode}, BARCODE@row, {Patient Visit & Treatment Log Pain Mgt D/C}, 1)), 1)))

Sam Lugiano

I just tried that out on one sheet and it works great. It was an easy add-on and shouldn't take long to do all the sheets. Thanks again for your help.

Sam

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.