"Next Due Date" formula using 3 Date Fields

[Nick_Robeson]

Hi all,

The review process for our Creative team typically consists of two reviews prior to the final asset being uploaded to Airtable. Because of our review process, the Project Manager is responsible for assigning three due dates to each line item (asset) within the Creative table: V1 Due Date, V2 Due Date, Final Due Date.

I would like to have the option to hide all three of the fields listed above and have a formula field that will display only the Next Due Date in a user friendly format. Please see below for the set up of the date columns mentioned with examples. The “Next Due Date” column is currently blank as I try to come up with this formula. A nested IF statement should do the trick, just haven’t yet figured it out!

Any help is greatly appreciated!

[Kamille_Parks]

Varying the solution from this thread slightly

Find the maximum of three date fields on one table Formulas

Alternate formula: IF( OR({Date 1}, {Date 2}, {Date 3}), DATETIME_PARSE( MAX( IF({Date 1}, VALUE(DATETIME_FORMAT({Date 1}, 'YYYYMMDD'))), IF({Date 2}, VALUE(DATETIME_FORMAT({Date 2}, 'YYYYMMDD'))), IF({Date 3}, VALUE(DATETIME_FORMAT({Date 3}, 'YYYYMMDD'))) ), 'YYYYMMDD' ) )

You could try the following. It finds the closest day including and after today. If no date is after today, the formula returns a blank value. If you don’t want deadlines that are “today”, change all the “>=” to just “>”.

IF(
   OR({Date 1} >= TODAY(), {Date 2} >= TODAY(), {Date 3} >= TODAY()), 
   DATETIME_PARSE(
      MIN(
         IF(AND({Date 1}, {Date 1} >= TODAY()), VALUE(DATETIME_FORMAT({Date 1}, 'YYYYMMDD'))), 
         IF(AND({Date 2}, {Date 2} >= TODAY()), VALUE(DATETIME_FORMAT({Date 2}, 'YYYYMMDD'))), 
         IF(AND({Date 3}, {Date 3} >= TODAY()), VALUE(DATETIME_FORMAT({Date 3}, 'YYYYMMDD')))
      ), 
      'YYYYMMDD'
   )
)