Forum Discussion
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:
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)
10 Replies
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.
- Sergei. Thanks ☺️
Clive_Rabson , glad to help, hope it works now.
Clive_Rabson Please see the attached file, which contains edits to both methods discussed previously...
