Forum Discussion
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)
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)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
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.
- You are adding me!? Haha
For the peak date it could be
=INDEX($D3:$AQ3, MATCH($AS3, $D3:$AQ3, 0) +1 )and drag it down
- Sergei. Thanks for all the help you have given me with this project. You are the best !
- Sergei. Thanks ☺️
Clive_Rabson , glad to help, hope it works now.
