Formula to find next upcoming date from 6 other date columns in a record

[kingsleyspencer]

In our scheduling table for projects (table is called "Deliverables"), we have 6 sequential dates that tend to move frequently:

1. Kickoff Date
2. Creative Connect
3. Round 1 to Client
4. Round 2 to Client
5. Round X to Client
6. Due Date

I've been trying to write a formula (fairly unsuccessfully after researching much of the forum) that will look at all six of these dates and return the closest date that is TODAY or later. All 6 dates are in a single table and apply to all records. We're putting them in a column named "Next Due Date"

What we currently have:

IF(
OR({Kickoff Date}, {Creative Connect}, {Round 1 to Client}, {Round 2 to Client}, {Round X to Client}, {Due Date}),
DATETIME_PARSE(
MIN(
IF({Kickoff Date}, (VALUE(DATETIME_FORMAT({Kickoff Date}, 'YYYYMMDD'))-VALUE(DATETIME_FORMAT(TODAY(), 'YYYYMMDD')))),
IF({Creative Connect}, (VALUE(DATETIME_FORMAT({Creative Connect}, 'YYYYMMDD'))-VALUE(DATETIME_FORMAT(TODAY(), 'YYYYMMDD')))),
IF({Round 1 to Client}, (VALUE(DATETIME_FORMAT({Round 1 to Client}, 'YYYYMMDD'))-VALUE(DATETIME_FORMAT(TODAY(), 'YYYYMMDD')))),
IF({Round 2 to Client}, (VALUE(DATETIME_FORMAT({Round 2 to Client}, 'YYYYMMDD'))-VALUE(DATETIME_FORMAT(TODAY(), 'YYYYMMDD')))),
IF({Round X to Client}, (VALUE(DATETIME_FORMAT({Round X to Client}, 'YYYYMMDD'))-VALUE(DATETIME_FORMAT(TODAY(), 'YYYYMMDD')))),
IF({Due Date}, (VALUE(DATETIME_FORMAT({Due Date}, 'YYYYMMDD'))-VALUE(DATETIME_FORMAT(TODAY(), 'YYYYMMDD'))))
),
'D/M/YYYY'
)
)

That's one version and we also tried some variations of this as well:

IF(
   OR({Kickoff Date}, {Creative Connect}, {Round 1 to Client}, {Round 2 to Client}, {Round X to Client}, {Due Date}), 
   DATETIME_PARSE(
      MIN(
         IF({Kickoff Date}, DATETIME_DIFF({Kickoff Date}, TODAY())), 
         IF({Creative Connect}, DATETIME_DIFF({Creative Connect}, TODAY())),
         IF({Round 1 to Client}, DATETIME_DIFF({Round 1 to Client}, TODAY())), 
         IF({Round 2 to Client}, DATETIME_DIFF({Round 2 to Client}, TODAY())), 
         IF({Round X to Client}, DATETIME_DIFF({Round X to Client}, TODAY())),  
         IF({Due Date}, DATETIME_DIFF({Due Date}, TODAY()))
      ), 
      'D/M/YYYY'
   )
)

Thanks!

[kingsleyspencer]

Might have gotten closer with this:

IF(
   OR({Kickoff Date}, {Creative Connect}, {Round 1 to Client}, {Round 2 to Client}, {Round X to Client}, {Due Date}), 
   MIN(
      IF(IS_AFTER({Kickoff Date}, TODAY()), {Kickoff Date}),
      IF(IS_AFTER({Creative Connect}, TODAY()), {Creative Connect}),
      IF(IS_AFTER({Round 1 to Client}, TODAY()), {Round 1 to Client}),
      IF(IS_AFTER({Round 2 to Client}, TODAY()), {Round 2 to Client}),
      IF(IS_AFTER({Round X to Client}, TODAY()), {Round X to Client}),
      IF(IS_AFTER({Due Date}, TODAY()), {Due Date})
   )
)

Really just firing in the dark at this point.

[Stephen_Orr1]

Hi @kingsleyspencer,
An alternate approach would be to create a roll-up field in your projects table that's pointed to this date field (via the Deliverables linked record field). Set the aggregate function to min(values) to get the earliest deliverable's due date. Also add a condition on this field where "the Status of the deliverable is not complete" so that the min(values) aggregation grabs the date from earliest non-complete deliverable.

If you still need this field in the Deliverables table, you can use a lookup field to pull it in.

Hope that helps!
-Stephen

[Stephen_Orr1]

Adding that the method I mentioned earlier assumes you have your base set up with the following linked tables:
Projects, Deliverables.

Having multiple date fields represent a single date (deliverable due date) makes it much more difficult to calendar these in Airtable and is not really best practice.

-Stephen

[kingsleyspencer]

I've seen a lot of rollup + min(values) but the dates are stored in 6 columns (per record). The Rollup only requests a single field and we need to look at all 6 dates—unless I just really really don't understand the Rollup field type.

That's been my main issue, along with the fact that MIN() doesn't see dates unless they're converted to in integer/number first. Side note: adding the IF(complete) to my formula is such a great thought I honestly would have kicked myself over—thinking we'd have to do that with an AND() at the beginning of the expression? Thanks for the help

[kingsleyspencer]

I generally agree, but our projects have clear start and end dates, but the milestones in-between those dates move and shift around a lot. They're generally more important to know for our production teams though thus the trying to make this "Next Due Date" field for an at-a-glance look. Proving to be extremely difficult without PHP/variables or them being in a simple XLS.

[Stephen_Orr1]

Project start and end dates can be stored independently in the Projects table. (You could even create these as roll-up fields to pull in the first and last deliverable/task start and end dates from the deliverables table, if they are always tied to deliverables/tasks).

Roll-up fields work the same as lookup fields (require a linked record field between two tables) but they allow an aggregation to be applied, grouped by the record in the table they are in.

The structure I suggested (linked Projects, Deliverables tables) lets base users adjust the dates of the deliverables in the Deliverables table independently of each other. There are many benefits to doing it this way, such as calendaring deliverables in a single calendar/timeline/gantt view. You can also assign deliverables to base collaborators via a collaborator field and enable automatic alerts. FYI -this is structuring your data in a relational way vs. flat like Excel and is how Airtable really shines as a tool.

Please feel free to copy this example base I made to demonstrate what I mean:
https://airtable.com/shrvo5EOwDeSKSLb7

Hope that helps!

[kingsleyspencer]

@Stephen_Orr1 Here's where I landed:

IF(
  OR({Kickoff Date}, {Creative Connect Date}, {R1 to Client Date}, {R2 to Client Date}, {RX to Client Date}, {Mech/Finalize Date}, {Due Date}),
  DATETIME_PARSE(
    MIN(
      IF(IS_AFTER({Kickoff Date}, TODAY()), VALUE(DATETIME_FORMAT({Kickoff Date}, 'x', 999999999999999))),
      IF(IS_AFTER({Creative Connect Date}, TODAY()), VALUE(DATETIME_FORMAT({Creative Connect Date}, 'x', 999999999999999))),
      IF(IS_AFTER({R1 to Client Date}, TODAY()), VALUE(DATETIME_FORMAT({R1 to Client Date}, 'x', 999999999999999))),
      IF(IS_AFTER({R2 to Client Date}, TODAY()), VALUE(DATETIME_FORMAT({R2 to Client Date}, 'x', 999999999999999))),
      IF(IS_AFTER({RX to Client Date}, TODAY()), VALUE(DATETIME_FORMAT({RX to Client Date}, 'x', 999999999999999))),
      IF(IS_AFTER({Mech/Finalize Date}, TODAY()), VALUE(DATETIME_FORMAT({Mech/Finalize Date}, 'x', 999999999999999))),
      IF(IS_AFTER({Due Date}, TODAY()), VALUE(DATETIME_FORMAT({Due Date}, 'x', 999999999999999)))
    ),
    'x'
  )
)

It absolutely does the job, but I have to add one more statement (that I haven't quite cracked yet). If the last date in the series is before today (aka there is no next due date) then it's showing 1/1/1970. HA

If you know what to throw on the end of that initial IF statement then I'm all ears. Thanks for the help initially though—massively helpful.