3 Replies Latest reply on Nov 8, 2013 11:43 AM by Matt Lutton

    How can I find the earliest date (via calculated field) from among a number of dates?

    Caleb Smith

      I'm trying to create a calculation that finds a number of records that match a certain criterion, and then keeps only the earliest date.

       

      I've attached a simplified version of the data in an excel file, but I'll give you an example here as well:

       

      I have a parent record with ID001. This parent record holds a set of states (as in status') along with the dates the record entered each state.

       

      ST001: Approved, 7/22/2010

      ST002: Expired, 8/10/2011

      ST003: Approved, 10/1/2011

      ST004: Terminated, 12/25/2012

      ST005: In Review, 1/1/2013

      ST006: Approved, 1/22/2013

       

      For my calculated field I want to find the earliest date that the record entered the "Approved" state. It's important to note that the number of states for each record will be different. Some records may have only entered one state in their lifetime, while others will have entered hundreds. Similarly, not all records will have states that meet the "Approved" criterion, while others will have multiple instances of it. And finally, the "ID" for each state is completely arbitrary and does not correspond to any reliable chronology. I can't rely on the ID to tell me which state came first, and which came last, I must rely on the date value.

       

      Any help would be greatly appreciated!