Forum Discussion
Solved
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...
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
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 !
Clive_Rabson , you are welcome, thank you