Help

Formula for choosing the earliest date with time

Topic Labels: Dates & Timezones
123 5
cancel
Showing results for 
Search instead for 
Did you mean: 

I’m having trouble coming up with a formula that chooses the earliest of up to 12 dates with times. I’ve seen other questions that are similar but this involves times too. The Time 1Time 12 variables are from a lookup in another table. They might be blank.

MIN(
VALUE(DATETIME_PARSE({Time 1},‘MMDYYYY hh:mm’),
VALUE(DATETIME_PARSE({Time 2},‘MMDYYYY hh:mm’),
VALUE(DATETIME_PARSE({Time 3},‘MMDYYYY hh:mm’…

^ Fails.

Thanks!

5 Replies 5

Welcome to the Airtable community!

Are they lookups in the same field? If so, you can use a rollup field.

However, because you have several different fields , I’m guessing a rollup won’t work for you.
Here is a different formula designed for four different date/time fields. You can extend the pattern to add more date/time fields. Note that it might not work for lookup fields, because lookup fields are quirky. You might also need to set the timezone formatting options on the formula field.

IF(
  OR(
    {Time 1},
    {Time 2},
    {Time 3},
    {Time 4}
  ),
  DATETIME_PARSE(
    MIN(
      IF({Time 1}, VALUE(DATETIME_FORMAT({Time 1}, 'x')), 999999999999999),
      IF({Time 2}, VALUE(DATETIME_FORMAT({Time 2}, 'x')), 999999999999999),
      IF({Time 3}, VALUE(DATETIME_FORMAT({Time 3}, 'x')), 999999999999999),
      IF({Time 4}, VALUE(DATETIME_FORMAT({Time 4}, 'x')), 999999999999999)
    ) & "",
    "x"
  )
)

Hi and TY for responding!

The dates and times are entered using the calendar entry tool. I’m simply reading them into the main table using a look-up. I then want a formula that chooses the earliest of those 12 dates+times.

Did you try the formula I suggested?

I tried it before and it failed on me but now I have it working and I’m expanding it!!! Stand by!!!

THANK YOU!!! It’s working just fine.