Most recent date formula that pulls the most recent date from a date column base different regions.

Wilson.springs

I have a sheet that has a "date entered" column and a "market column" that has different regions listed under the market column. I am trying to pull the most recent date for each market based on the date entered column. I need a formula that can look at each row based on a specific set of markets and pull the most recent date from that specific region in the market group column.

DKazatsky2

Hi @Wilson.springs ,

Are you trying to get the value in every row or just a single value somewhere?

The following formula will give the max date for whatever is listed in the "Market Column", so it would show a value in each row.

=MAX(COLLECT([Date Entered]:[Date Entered], [Market Column]:[Market Column], @cell = [Market Column]@row))

If you want to do this in just on cell separetley, replace @cell = [Market Column]@row) with @cell = "Region Name Here"

Hope this helps,

Dave

Nick Korna

Hi @Wilson.springs,

If you're looking to only do one market option from the list:

=MAX(COLLECT([Date Entered]:[Date Entered], Market:Market, "A"))

If you wanted multiple, then you can change the last bit to an OR:

=MAX(COLLECT([Date Entered]:[Date Entered], Market:Market, OR(@cell = "A", @cell = "B")))

You can do this with sheet cross references in a similar formula if you wanted an easily viewed tabe (where the reference refers to the column you'd highlight for that reference):

=MAX(COLLECT({Date}, {Market}, Market@row))

Samples:

On one sheet (top 2 formulae):

As a cross sheet (last formula):

Hope this helps, but if you've any problems/questions then let us know.

Wilson.springs

That did not work. Let me try a better explanation. Here is the formula that is bombing. I have two sheets. In this sheet the incorrect argument cell error is below. It references date entered and market column on the other sheet below. The most recent value in the first sheet is calculated using a max formula.

=INDEX(COLLECT({Gross Metro Product2}, {Date Entered}, [Most Recent]1, {Market2}, Columbia1))

Any Thoughts?

Nick Korna

@Wilson.springs, add in , 1 into the formula:

=INDEX(COLLECT({Gross Metro Product}, {Most Recent}, [Most Recent Date]1, {Market}, [Company]1), 1)

For your formula, the slight alteration would be:

=INDEX(COLLECT({Gross Metro Product2}, {Date Entered}, [Most Recent]1, {Market2}, Columbia1), 1)

Data sample:

Cross sheet with lookups:

Most recent date is using:

=MAX(COLLECT({Most Recent}, {Market}, [Company]1))

Hopefully this should help - let me know how it goes. 🙂