The Airtable Community will undergo scheduled maintenance on September 17 from 10:00 PM PST to 11:15 PM PST. During this period, you may experience temporary disruptions. We apologize for any inconvenience and appreciate your understanding.

# How to get Earliest or Latest date from multiple dates in a cell.

Topic Labels: Formulas
Solved
1652 3
cancel
Showing results for
Did you mean:
4 - Data Explorer

I am trying to get Earliest or Latest date from a field (example: 2022-10-13, 2022-10-15). When I use Min or Max formulas - I get zeros. (I marked those areas as red)

Looking forward for solutions. Thank you!

To better understand my case I have prepared how everything works in my Airtable:

First sheet is for a/b test Data. We import all a/b tests information here.

 Single line text Link to Other sheet Single line text Formula Formula Locale Link to Flows A/B Test Dates A/B Test Start Date A/B Test End Date FR Countdown timer 10/13/2022 - 01/02/2023 2022-10-13 2023-01-02 DE Countdown timer 10/15/2022 - 01/05/2023 2022-10-15 2023-01-05 Countdown timer test runs on multiple locales. in this case - French and German locales. I receive it in such format I use this formula for this:DATESTR(IF({A/B Test Dates}, REGEX_EXTRACT({A/B Test Dates}, "[^ ]*"))) I use this formula for this:DATESTR(IF({A/B Test Dates}, REGEX_EXTRACT({A/B Test Dates}, "[^ ]*\$")))

Second sheet, which is rolls up all the information. One line / test. with all locales information.

 Rollup (first sheet Start Date) Rollup (first sheet End Date) Link to previous sheet tests Start Date End Date Countdown timer - FR, Countdown timer - DE 0 0 MIN(values) MAX(values) 2022-10-13, 2022-10-15 if used ARRAYUNIQUE(values) 2023-01-02, 2023-01-05 if used ARRAYUNIQUE(values)
1 Solution

Accepted Solutions
11 - Venus

Hey @paulius

The immediate reason why the MIN(values) & MAX(values) functions are not returning as expected is because you're not returning a date/time data type from your A/B Test Start Date and A/B Test End Date formula fields.

As expected, the DATESTR() function formats date values as strings.
With an adjustment in your formulas, I was able to get the following behavior by leaning on the DATETIME_PARSE() function.

Here's what the formulae looks like:

A/B Test Start Date

``````IF(
{A/B Test Dates},
IF(
REGEX_MATCH(
{A/B Test Dates}, "^\\d{1,2}/\\d{1,2}/\\d{4}\\s"
),
DATETIME_PARSE(
REGEX_EXTRACT(
{A/B Test Dates}, "^\\d+/\\d+/\\d{4}"
)
)
)
)``````

A/B Test End Date

``````IF(
{A/B Test Dates},
IF(
REGEX_MATCH(
{A/B Test Dates}, "\\d{1,2}/\\d{1,2}/\\d{4}\$"
),
DATETIME_PARSE(
REGEX_EXTRACT(
{A/B Test Dates}, "\\d+/\\d+/\\d{4}\$"
)
)
)
)``````

You might notice the mismatched regex patterns I passed for the regex function parameters.
There's no real purpose. I was just playing around with different patterns that might match the desired string.
Feel free to adjust it as you wish.

3 Replies 3
11 - Venus

Hey @paulius

The immediate reason why the MIN(values) & MAX(values) functions are not returning as expected is because you're not returning a date/time data type from your A/B Test Start Date and A/B Test End Date formula fields.

As expected, the DATESTR() function formats date values as strings.
With an adjustment in your formulas, I was able to get the following behavior by leaning on the DATETIME_PARSE() function.

Here's what the formulae looks like:

A/B Test Start Date

``````IF(
{A/B Test Dates},
IF(
REGEX_MATCH(
{A/B Test Dates}, "^\\d{1,2}/\\d{1,2}/\\d{4}\\s"
),
DATETIME_PARSE(
REGEX_EXTRACT(
{A/B Test Dates}, "^\\d+/\\d+/\\d{4}"
)
)
)
)``````

A/B Test End Date

``````IF(
{A/B Test Dates},
IF(
REGEX_MATCH(
{A/B Test Dates}, "\\d{1,2}/\\d{1,2}/\\d{4}\$"
),
DATETIME_PARSE(
REGEX_EXTRACT(
{A/B Test Dates}, "\\d+/\\d+/\\d{4}\$"
)
)
)
)``````

You might notice the mismatched regex patterns I passed for the regex function parameters.
There's no real purpose. I was just playing around with different patterns that might match the desired string.
Feel free to adjust it as you wish.

4 - Data Explorer

AMAZING! Thank you so much! 😍

6 - Interface Innovator

Hi!

@Ben_Young1 , I did the same formula in the field "Start Date" in my table, but the resulting cells returns only blank.  Any hint why?

My intention is to obtain the earliest "Start Date" and the latest "End Date" from some registers groupped by another field in my table, called "MODULE".
I want to know when that "MODULE" is expected to start and to finish.

I changed your formula this way:

IF(
{Start date},
IF(
REGEX_MATCH(
{Start date}, "^\\d{1,2}/\\d{1,2}/\\d{4}\\s"
),
DATETIME_PARSE(
REGEX_EXTRACT(
{Start date}, "^\\d+/\\d+/\\d{4}"
)
)
)
)

In the print below I try to clarify my intention.

Thank you!