Skip to main content
Solved

Formula to choose Earliest Date from list of 6 dates

  • July 15, 2020
  • 4 replies
  • 260 views
M

Hi everyone! I have a table with a list of 6 different release dates - I’m trying to create a field that looks at all of those dates and picks the earliest/first one as “Initial Launch.” IS_BEFORE only seems to work with 2 dates and the factorial of 6 dates is 720 possibilities of combinations so I don’t think I can do a workaround combining multiple nested IF and IS_BEFORE functions. Maybe I’m over thinking this though. I also thought about self-linking and doing a rollup field?

Anyway, any advice or recommendations welcome!

Best answer by ScottWorld

Someone might have a quicker & easier way of accomplishing this, but this is how I would personally do it on my end:

The MIN function is designed to compare a whole bunch of different numbers and return the minimum value of those numbers. It would be amazing if the MIN function worked on date fields, but it doesn’t.

So we need to convert your dates to pure numbers, and those numbers should be in a format where a later date is numerically larger than an earlier date.

So, for today (July 15, 2020), we would want today’s number to look like 20200715, which is the format YYYYMMDD. We can do this with the DATETIME_FORMAT function, but that returns a text string, so we need to wrap the results of that function in VALUE to turn it into a number.

So the formula to convert one specific date into a number would look like this:
VALUE(DATETIME_FORMAT({Date Field #1},'YYYYMMDD'))

To compare a whole bunch of date fields and return the minimum of those date fields, you would use this formula:

MIN(
VALUE(DATETIME_FORMAT({Date Field #1},'YYYYMMDD')),
VALUE(DATETIME_FORMAT({Date Field #2},'YYYYMMDD')),
VALUE(DATETIME_FORMAT({Date Field #3},'YYYYMMDD')),
VALUE(DATETIME_FORMAT({Date Field #4},'YYYYMMDD')),
VALUE(DATETIME_FORMAT({Date Field #5},'YYYYMMDD')),
VALUE(DATETIME_FORMAT({Date Field #6},'YYYYMMDD'))
)

That will give you the result that you’re looking for, but it will be in the format of YYYYMMDD.

So, to convert it back into a readable date format again, you would create ANOTHER formula field that would look like this:

DATETIME_PARSE({Name of your formula field above}, 'YYYYMMDD')

And that will get you what you need! :slightly_smiling_face:

Hope this helps! If this answers your question, could you please mark this comment as the solution to your question? This will help other people who have a similar question. :slightly_smiling_face:

    D
    J

    4 replies

    ScottWorld
    Forum|alt.badge.img+35
    • ScottWorld
    • Genius
    • Answer
    • July 15, 2020

    Someone might have a quicker & easier way of accomplishing this, but this is how I would personally do it on my end:

    The MIN function is designed to compare a whole bunch of different numbers and return the minimum value of those numbers. It would be amazing if the MIN function worked on date fields, but it doesn’t.

    So we need to convert your dates to pure numbers, and those numbers should be in a format where a later date is numerically larger than an earlier date.

    So, for today (July 15, 2020), we would want today’s number to look like 20200715, which is the format YYYYMMDD. We can do this with the DATETIME_FORMAT function, but that returns a text string, so we need to wrap the results of that function in VALUE to turn it into a number.

    So the formula to convert one specific date into a number would look like this:
    VALUE(DATETIME_FORMAT({Date Field #1},'YYYYMMDD'))

    To compare a whole bunch of date fields and return the minimum of those date fields, you would use this formula:

    MIN(
VALUE(DATETIME_FORMAT({Date Field #1},'YYYYMMDD')),
VALUE(DATETIME_FORMAT({Date Field #2},'YYYYMMDD')),
VALUE(DATETIME_FORMAT({Date Field #3},'YYYYMMDD')),
VALUE(DATETIME_FORMAT({Date Field #4},'YYYYMMDD')),
VALUE(DATETIME_FORMAT({Date Field #5},'YYYYMMDD')),
VALUE(DATETIME_FORMAT({Date Field #6},'YYYYMMDD'))
)

    That will give you the result that you’re looking for, but it will be in the format of YYYYMMDD.

    So, to convert it back into a readable date format again, you would create ANOTHER formula field that would look like this:

    DATETIME_PARSE({Name of your formula field above}, 'YYYYMMDD')

    And that will get you what you need! :slightly_smiling_face:

    Hope this helps! If this answers your question, could you please mark this comment as the solution to your question? This will help other people who have a similar question. :slightly_smiling_face:

    Ricardo11
    kuovonne
    D
    M
    • Mariah_Gregory
    • Author
    • New Participant
    • July 15, 2020

    Someone might have a quicker & easier way of accomplishing this, but this is how I would personally do it on my end:

    The MIN function is designed to compare a whole bunch of different numbers and return the minimum value of those numbers. It would be amazing if the MIN function worked on date fields, but it doesn’t.

    So we need to convert your dates to pure numbers, and those numbers should be in a format where a later date is numerically larger than an earlier date.

    So, for today (July 15, 2020), we would want today’s number to look like 20200715, which is the format YYYYMMDD. We can do this with the DATETIME_FORMAT function, but that returns a text string, so we need to wrap the results of that function in VALUE to turn it into a number.

    So the formula to convert one specific date into a number would look like this:
    VALUE(DATETIME_FORMAT({Date Field #1},'YYYYMMDD'))

    To compare a whole bunch of date fields and return the minimum of those date fields, you would use this formula:

    MIN(
VALUE(DATETIME_FORMAT({Date Field #1},'YYYYMMDD')),
VALUE(DATETIME_FORMAT({Date Field #2},'YYYYMMDD')),
VALUE(DATETIME_FORMAT({Date Field #3},'YYYYMMDD')),
VALUE(DATETIME_FORMAT({Date Field #4},'YYYYMMDD')),
VALUE(DATETIME_FORMAT({Date Field #5},'YYYYMMDD')),
VALUE(DATETIME_FORMAT({Date Field #6},'YYYYMMDD'))
)

    That will give you the result that you’re looking for, but it will be in the format of YYYYMMDD.

    So, to convert it back into a readable date format again, you would create ANOTHER formula field that would look like this:

    DATETIME_PARSE({Name of your formula field above}, 'YYYYMMDD')

    And that will get you what you need! :slightly_smiling_face:

    Hope this helps! If this answers your question, could you please mark this comment as the solution to your question? This will help other people who have a similar question. :slightly_smiling_face:


    This worked! Thanks, I tried something similar but I was missing the VALUE() function in my formula.

    ScottWorld
    Justin_Barrett
    Forum|alt.badge.img+21

    Someone might have a quicker & easier way of accomplishing this, but this is how I would personally do it on my end:

    The MIN function is designed to compare a whole bunch of different numbers and return the minimum value of those numbers. It would be amazing if the MIN function worked on date fields, but it doesn’t.

    So we need to convert your dates to pure numbers, and those numbers should be in a format where a later date is numerically larger than an earlier date.

    So, for today (July 15, 2020), we would want today’s number to look like 20200715, which is the format YYYYMMDD. We can do this with the DATETIME_FORMAT function, but that returns a text string, so we need to wrap the results of that function in VALUE to turn it into a number.

    So the formula to convert one specific date into a number would look like this:
    VALUE(DATETIME_FORMAT({Date Field #1},'YYYYMMDD'))

    To compare a whole bunch of date fields and return the minimum of those date fields, you would use this formula:

    MIN(
VALUE(DATETIME_FORMAT({Date Field #1},'YYYYMMDD')),
VALUE(DATETIME_FORMAT({Date Field #2},'YYYYMMDD')),
VALUE(DATETIME_FORMAT({Date Field #3},'YYYYMMDD')),
VALUE(DATETIME_FORMAT({Date Field #4},'YYYYMMDD')),
VALUE(DATETIME_FORMAT({Date Field #5},'YYYYMMDD')),
VALUE(DATETIME_FORMAT({Date Field #6},'YYYYMMDD'))
)

    That will give you the result that you’re looking for, but it will be in the format of YYYYMMDD.

    So, to convert it back into a readable date format again, you would create ANOTHER formula field that would look like this:

    DATETIME_PARSE({Name of your formula field above}, 'YYYYMMDD')

    And that will get you what you need! :slightly_smiling_face:

    Hope this helps! If this answers your question, could you please mark this comment as the solution to your question? This will help other people who have a similar question. :slightly_smiling_face:


    Just to clarify, MIN() and MAX() won’t work when referencing date fields directly. However, when a bunch of dates have been collected in a rollup, MIN(values) will work to find the earliest date, and MAX(values) can find the latest.

    J
    K
    S
    kuovonne
    Forum|alt.badge.img+29
    • kuovonne
    • Brainy
    • July 18, 2020

    @ScottWorld came up with a great formula. Here is a variation. My variation is a bit overkill for the original situation, but it might be useful in other similar situations with a greater variety in date/times.

    • This version retains the original time (down to the millisecond) as well as the date of the original date/time field. It does this by formatting the date/time as a Unix timestamp with the unit specifiier 'x'. Using the Unix timestamp also enables the formula to work with years before 1000AD.

    • This formula converts the numeric value back into a date, so you do not need an extra field for that step.

    • This formula also checks to make sure that there is a date in at least one of the fields, and finds the earliest of whichever fields do have dates in them. (It is blank if all date fields are blank.)

    IF(
  OR(
    {Date Field #1},
    {Date Field #2},
    {Date Field #3},
    {Date Field #4},
    {Date Field #5},
    {Date Field #6}
  ),
  DATETIME_PARSE(
    MIN(
      IF({Date Field #1}, VALUE(DATETIME_FORMAT({Date Field #1},'x')), 999999999999999),
      IF({Date Field #2}, VALUE(DATETIME_FORMAT({Date Field #2},'x')), 999999999999999),
      IF({Date Field #3}, VALUE(DATETIME_FORMAT({Date Field #3},'x')), 999999999999999),
      IF({Date Field #4}, VALUE(DATETIME_FORMAT({Date Field #4},'x')), 999999999999999),
      IF({Date Field #5}, VALUE(DATETIME_FORMAT({Date Field #5},'x')), 999999999999999),
      IF({Date Field #6}, VALUE(DATETIME_FORMAT({Date Field #6},'x')), 999999999999999)
    ) ,
    'x'
  )
)
    ScottWorld
    Ricardo11
    J
    Terms & ConditionsAccessibility statement