Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

Date Selection from three options

Topic Labels: Formulas
Solved
Jump to Solution
390 2
cancel
Showing results for 
Search instead for 
Did you mean: 
HannahS
6 - Interface Innovator
6 - Interface Innovator

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 

1 Solution

Accepted Solutions
Alexey_Gusev
13 - Mars
13 - Mars

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.


See Solution in Thread

2 Replies 2
Alexey_Gusev
13 - Mars
13 - Mars

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.


HannahS
6 - Interface Innovator
6 - Interface Innovator

This is fantastic, thanks so much for your help!