AnsweredAssumed Answered

Find next oldest year in a range of records

Question asked by Stu412 on Jun 22, 2015
Latest reply on Jun 22, 2015 by philmodjunk


Find next oldest year in a range of records


Hi there

I have a data table and on every records there is a date.  The date can be the same for many records.  As part of what I need to do, I've been required to find the most recent date, so I've simply used the formula Max(datefield) which works.  I now need to find the next most recent date which I'm unsure how to do.  An extract of data would look like this:

Amount | Date 

100       | 31/01/2015

210       | 31/01/2015

450       | 31/01/2015

300       | 31/12/2014

My Maxdate formula returns 31/01/2015 and now I need to get a second formula to pick up the bottom row and pull through the 31/12/2014.

It's not simply a case of (Date-One month) because the dates can vary.  I need FM to calculate the most recent date and from there check for the next most recent date, however long ago that may be.