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

Topic Labels: Formulas
Solved
629 2
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  10 - Mercury

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.

2 Replies 2  10 - Mercury

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! 😍 