Skip to main content

I'm tracking book publications and each of the titles in my database will have several dates for different formats - Hardback, paperback, ebook and Audio...I'm trying to create a formula that will automatically select the earliest of these dates so that I can use that to trigger something else.

I've searched other questions and found this:

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')) )

I feel like this is partway there but because some books don’t have all formats some of these fields will be blank for some titles and where that’s the case I just get an error message. Is there a way to tell it to ignore any blank fields?

Thanks 

Hi,
You shoud wrap each of 4 lines into IF(Field, _current_line_ )

Like this: ( i would suggest to move first '('  up to MIN  )


MIN(
  IF( {Date Field #1}. VALUE(DATETIME_FORMAT({Date Field #1},'YYYYMMDD')) ),
otjer three.....
)


In the end,  you will receive something like 20241207 - number type
So, finally you should wrap it all into DATETIME_PARSE(
...... , 'YYYYMMDD' )  to get date.
 
To format result value, use 'Formatting' in a field edit.


Hi,
You shoud wrap each of 4 lines into IF(Field, _current_line_ )

Like this: ( i would suggest to move first '('  up to MIN  )


MIN(
  IF( {Date Field #1}. VALUE(DATETIME_FORMAT({Date Field #1},'YYYYMMDD')) ),
otjer three.....
)


In the end,  you will receive something like 20241207 - number type
So, finally you should wrap it all into DATETIME_PARSE(
...... , 'YYYYMMDD' )  to get date.
 
To format result value, use 'Formatting' in a field edit.



This is fantastic, thanks so much for your help!

Reply


Terms & Conditions