Looking for last date before today, referencing unique identifier

AHANSON

Hi, I'm looking for a formula that will reference a unique identifier (Unique Object ID) in a list of other Object IDs, and collect the last date but that is less than today. The following formula works to return the last date, but some return a future date which I don't want. I need the date that is closest to today but is not in the future.

So far I have this and it returns the last date, referencing the Unique Object ID = Patient ID:

=MAX(COLLECT({240 Data Mapping by Patient Range 13}, {240 Data Mapping by Patient Range 6}, [Patient ID]@row))

I tried adding the @cell <= today() but it didn't work:

=MAX(COLLECT({240 Data Mapping by Patient Range 13}, {240 Data Mapping by Patient Range 6}, [Patient ID]@row), @cell <= TODAY())

Thanks in advance!

Paul Newcome

Assuming that first range is the date range, it would look somethign like this...

=MAX(COLLECT({240 Data Mapping by Patient Range 13}, {240 Data Mapping by Patient Range 13}, @cell < TODAY(), {240 Data Mapping by Patient Range 6}, [Patient ID]@row))

AHANSON

This worked beautifully. Thank you!

Paul Newcome

Assuming that first range is the date range, it would look somethign like this...

=MAX(COLLECT({240 Data Mapping by Patient Range 13}, {240 Data Mapping by Patient Range 13}, @cell < TODAY(), {240 Data Mapping by Patient Range 6}, [Patient ID]@row))

AHANSON

This worked beautifully. Thank you!

Paul Newcome

Happy to help. 👍️