Finding the earliest delivery date based on Material code as the criteria using MAX(COLLECT

Elliot Hamilton

I have a source sheet which is basically a delivery schedule for all materials with multiple delivery dates, what I want to pull to the target sheet is the earliest delivery date of that material code. Currently I have the following formula which doesn't seem to work!

=MAX(COLLECT({EDD}, {STYLE}, [MATERIAL CODE]@row), 1)

EDD (Delivery date column in source sheet), Style ( Material code column in source sheet), Material code row in Target sheet.

For context there are multiple delivery dates per material code in the delivery schedule so I only want to pull the earliest delivery date based on that material code.

Hoping all makes sense any help would be appreciated!

Thanks again

Elliot

David Joyeuse

Hi @Elliot Hamilton

If you want the earliest, then you should use MIn instead of MAX. MAX will return the latest date.

Watch out also on what kind of earliest date you want to return. The one that ever happened, or the next one to come. Would change the COLLECT a little bit.

=MIN(COLLECT({EDD}, {STYLE}, [MATERIAL CODE]@row))

Should do it.