Forum Discussion
Solved
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
thanks in advance
| 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 |
=MAXIFS(Date_out_column,Barcode_column,Barcode,Date_out_column,"<"&Returned_date)
3 Replies
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)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
=MAXIFS(Date_out_column,Barcode_column,Barcode,Date_out_column,"<"&Returned_date)
