Skip to main content

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 textLink to Other sheetSingle line textFormulaFormula
LocaleLink to FlowsA/B Test DatesA/B Test Start DateA/B Test End Date
FRCountdown timer10/13/2022 - 01/02/20232022-10-13 2023-01-02
DECountdown timer10/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 formatI 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 testsStart DateEnd Date
Countdown timer - FR, Countdown timer - DE  
 00
 MIN(values)MAX(values)
   
 2022-10-13, 2022-10-15 if used ARRAYUNIQUE(values)2023-01-02, 2023-01-05 if used ARRAYUNIQUE(values)

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. 

AMAZING! Thank you so much! 😍

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!

Reply


Terms & Conditions