SOLVED

MAX date when less than another date

Copper Contributor

MAX date when less than another date

hi, im looking for a formula that will find the max date in a range where the date is less than another cell.

example below of what im looking for (last_out date (expected result)

i am trying to find the MAX date_out for each barcode number which is not greater than returned date

 Barcode Date_out Barcode Returned Date Last_Out date (expected result) 5556666 20/12/2021 5556666 06/04/2023 06/01/2023 5556666 06/01/2023 444888 02/01/2023 20/12/2021 444888 20/12/2021 444888 06/01/2023
3 Replies
best response confirmed by Hans Vogelaar (MVP)
Solution

Re: MAX date when less than another date

``=MAXIFS(Date_out_column,Barcode_column,Barcode,Date_out_column,"<"&Returned_date)``

Re: MAX date when less than another date

@thomaspowell250 Following both formula should work-

``````=MAX(FILTER(\$B\$2:\$B\$5,(\$A\$2:\$A\$5=D2)*(\$B\$2:\$B\$5<E2)))
=MAXIFS(\$B\$2:\$B\$5,\$A\$2:\$A\$5,D2,\$B\$2:\$B\$5,"<"&E2)``````

Re: MAX date when less than another date

thank you both, it worked, i had the same formula but as the data is part of a table i was using the @column instead of the \$B\$5:...... which was giving me the error.

thanks again