- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Sep 29, 2022 01:33 PM
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 1 … Time 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!
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Sep 29, 2022 02:20 PM
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"
)
)
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Sep 29, 2022 02:27 PM
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.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Sep 29, 2022 02:30 PM
Did you try the formula I suggested?
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Sep 29, 2022 02:37 PM
I tried it before and it failed on me but now I have it working and I’m expanding it!!! Stand by!!!
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Sep 29, 2022 02:47 PM
THANK YOU!!! It’s working just fine.