Finding the Latest date in a range of dates

[Clive_Rabson]

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: 

[#2]

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

[#3 · Accepted solution]

@Clive_Rabson 

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)

[#4]

Sergei. Thanks ☺️

[#5]

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

[#6]

@SergeiBaklan 

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

[#7]

@Clive_Rabson 

For the peak date it could be

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

and drag it down

[#8]

@Clive_Rabson 

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.

[#9]

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

[#10]

You are adding me!? Haha

[#11]

@Clive_Rabson , you are welcome, thank you