MAX DATE FROM 3 COLUMNS

Sam Lugiano

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

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

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

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