How do I compare dates to return the lowest?

Hi,

In your case, I would transfer values to numbers

DATETIME_FORMAT(date,'X')

, count min and then transfer again 
 

DATETIME_PARSE(MIN(
...date1,
...date2,
....
), 'X' )

To avoid errors for absent values, lines for date fields might be smth like
 

IF(date1,VALUE(DATETIME_FORMAT(date1,'X'))),
IF(date2,VALUE(DATETIME_FORMAT(date2,'X'))),

And to prevent error for empty records, begin formula with 

IF(OR(date1,date2,date3…..
  DATETIME_PARSE(MIN(
   .....date1
   .....date2

Actuallly you can copy-paste line for first date several times and thern use Ctrl+H ‘Replace’ in Formula editor to easily create final formula.

Note: when finished, you should be able to change formula field formatting as date. Uncheck ‘Use the same time zone...’ to avoid zone shifting in result

​@PeteFord 
I think in Excel we can use min to determine the lowest value but here I think it wont work. Let's say if you have date 1, date 2, date 3 and date 4, then you can use the following formula
 You can replace the name with the actual column name!

IF(AND({Date 1}, {Date 2}, {Date 3}, {Date 4}), IF(IS_BEFORE(IF(IS_BEFORE({Date 1}, {Date 2}), {Date 1}, {Date 2}), IF(IS_BEFORE({Date 3}, {Date 4}), {Date 3}, {Date 4})), IF(IS_BEFORE({Date 1}, {Date 2}), {Date 1}, {Date 2}), IF(IS_BEFORE({Date 3}, {Date 4}), {Date 3}, {Date 4})), IF(AND({Date 1}, {Date 2}, {Date 3}), IF(IS_BEFORE(IF(IS_BEFORE({Date 1}, {Date 2}), {Date 1}, {Date 2}), {Date 3}), IF(IS_BEFORE({Date 1}, {Date 2}), {Date 1}, {Date 2}), {Date 3}), IF(AND({Date 1}, {Date 2}), IF(IS_BEFORE({Date 1}, {Date 2}), {Date 1}, {Date 2}), IF({Date 1}, {Date 1}, {Date 2}))))

​@PeteFord 

Below is the formula that you can use if you wanted to compare 4 dates to see which one is the earliest date.

(You can easily expand onto this formula to compare 5 dates, 6 dates, 7 dates, etc.)

However, note that this formula requires all 4 dates to be entered into Airtable. If all 4 date fields are not filled out, this formula will return nothing.

IF(
  AND({Date 1},{Date 2},{Date 3},{Date 4}),

DATETIME_PARSE(

MIN(
  VALUE(DATETIME_FORMAT({Date 1},'X')),
  VALUE(DATETIME_FORMAT({Date 2},'X')),
  VALUE(DATETIME_FORMAT({Date 3},'X')),
  VALUE(DATETIME_FORMAT({Date 4},'X'))
  )

,"X")

)

Hope this helps! If you’d like to hire the best Airtable consultant to help you with anything Airtable-related, please feel free to contact me through my website: Airtable consultant — ScottWorld

​@PeteFord 

Also, I should point out that even though my formula above will give you what you’re looking for, this is actually not the best way for you to setup a database.

Whenever you find yourself repeating a value over & over & over again within the same table, what that indicates is that you should move all of those values into their own linked table as separate records.

So, in your case, all of your date fields should each become their own record in another table, which you would then link to the master record in the original table.

Then, you could very easily do things like use Lookup fields and Rollup fields to calculate the earliest date, calculate the latest date, search across dates, etc.

Hope this helps! If you’d like to hire the best Airtable consultant to help you with anything Airtable-related, please feel free to contact me through my website: .https://www.scottworld.com/airtable 

Putting together Alexey’s formula would look something like this:

IF(
  OR(
    {Presale 1 Start},
    {Presale 2 Start},
    {Presale 3 Start},
    {Presale 4 Start},
    {Presale 5 Start}
  ),
  DATETIME_PARSE(
    MIN(
      IF({Presale 1 Start},VALUE(DATETIME_FORMAT({Presale 1 Start},'X'))),
      IF({Presale 2 Start},VALUE(DATETIME_FORMAT({Presale 2 Start},'X'))),
      IF({Presale 3 Start},VALUE(DATETIME_FORMAT({Presale 3 Start},'X'))),
      IF({Presale 4 Start},VALUE(DATETIME_FORMAT({Presale 4 Start},'X'))),
      IF({Presale 5 Start},VALUE(DATETIME_FORMAT({Presale 5 Start},'X')))
    ), 
    'X' 
  )
)

If this looks right do mark his post as the solution; just wanted to provide a working example of the script in case it was helpful!