Re: Formula for choosing the earliest date with time

1505 1
cancel
Showing results for
Did you mean:
5 - Automation Enthusiast

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
18 - Pluto

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"
)
)

5 - Automation Enthusiast

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.

18 - Pluto

Did you try the formula I suggested?

5 - Automation Enthusiast

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

5 - Automation Enthusiast

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