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

[paulius]

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)

[Ben_Young1 · Accepted solution]

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. 

[paulius]

AMAZING! Thank you so much! 😍

[NLOIA]

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!