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
Best Answer
-
@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])
Answers
-
@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])
-
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)))
-
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
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!