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

paulius · ‎Feb 26, 2023

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 · ‎Feb 26, 2023

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.

See Solution in Thread

Ben_Young1 · ‎Feb 26, 2023