SOLVED

# Finding the Latest date in a range of dates

Brass Contributor

# Finding the Latest date in a range of dates

With thanks to:

Peter Bartholomew; djclements

and Sergei Baklan.

I am nearly finished on this project that must find the earliest, latest and peak dates in a range of dates.
I now need to adapt the formula to be used on the Latest dates, which are always three cells on from each of the earliest dates in the range.

Will I now be able to find the latest date (by day and month only) if I can adapt the formula that was successfully deployed for Earliest dates?

This is the formula being used for identifying Earliest dates:

=AGGREGATE(15,6, \$D3:\$AQ3 /( (MONTH(\$D3:\$AQ3)*100+DAY(\$D3:\$AQ3)) = AGGREGATE(15,6,( MONTH(\$D3:\$AQ3)*100+DAY(\$D3:\$AQ3) )/NOT( MOD(COLUMN(\$D3:\$AQ3),4) ),1)  ),1)

Here is the table so far:

10 Replies

# Re: Finding the Latest date in a range of dates

@Clive_Rabson Please see the attached file, which contains edits to both methods discussed previously...

best response confirmed by Clive_Rabson (Brass Contributor)
Solution

# Re: Finding the Latest date in a range of dates

Criteria which columns to select is in MOD( COLUMN(...

To select every 4th row in the range we may use criteria

MOD(COLUMN(\$D3:\$AQ3) - COLUMN(\$D3)+1,4)=0

entire formula

``=AGGREGATE(14,6, \$D3:\$AQ3 /( (MONTH(\$D3:\$AQ3)*100+DAY(\$D3:\$AQ3)) = AGGREGATE(14,6,( MONTH(\$D3:\$AQ3)*100+DAY(\$D3:\$AQ3) )/(MOD(COLUMN(\$D3:\$AQ3) - COLUMN(\$D3)+1,4)=0),1)  ),1)``

# Re: Finding the Latest date in a range of dates

Sergei. Thanks ☺️

# Re: Finding the Latest date in a range of dates

@Clive_Rabson , glad to help, hope it works now.

# Re: Finding the Latest date in a range of dates

I thought I could use one of the earlier formula suggestions from you and DJClements to obtain the PeakDate. But I just get #SPILL which is beyond me.

I need to get the date of the first peak number. In the attached sheet, for the row3 this is found in cell M3 (16) and the date is in N3 ( 08/09/2012).

I think that will be the last part of this jigsaw.

Grateful for any suggestion

# Re: Finding the Latest date in a range of dates

For the peak date it could be

``=INDEX(\$D3:\$AQ3, MATCH(\$AS3, \$D3:\$AQ3, 0) +1 )``

and drag it down

# Re: Finding the Latest date in a range of dates

By the way, if you have #SPILL! error that means you are on Excel 365 (or at least 2021). With that formulae could be modified, moreover we may return all result by one formula.

But since you almost finished the project, perhaps above is for future improvements.

# Re: Finding the Latest date in a range of dates

Sergei. Thanks for all the help you have given me with this project. You are the best !

# Re: Finding the Latest date in a range of dates

You are adding me!? Haha

# Re: Finding the Latest date in a range of dates

@Clive_Rabson , you are welcome, thank you

1 best response

Accepted Solutions
best response confirmed by Clive_Rabson (Brass Contributor)
Solution

# Re: Finding the Latest date in a range of dates

Criteria which columns to select is in MOD( COLUMN(...

To select every 4th row in the range we may use criteria

MOD(COLUMN(\$D3:\$AQ3) - COLUMN(\$D3)+1,4)=0

entire formula

``=AGGREGATE(14,6, \$D3:\$AQ3 /( (MONTH(\$D3:\$AQ3)*100+DAY(\$D3:\$AQ3)) = AGGREGATE(14,6,( MONTH(\$D3:\$AQ3)*100+DAY(\$D3:\$AQ3) )/(MOD(COLUMN(\$D3:\$AQ3) - COLUMN(\$D3)+1,4)=0),1)  ),1)``