Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Formula to choose Earliest Date (some fields are blank)

Topic Labels: Dates & Timezones
649 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Jobelle_Herrera
4 - Data Explorer
4 - Data Explorer

Hi All,

I have a scenario where I need to have a formula that looks at all of the dates and picks up the earliest/first one as the "Initial Enquiry’. However not all of the fields are filled in there are fields that are empty as not everyone was enquiring for the particular developments.

I have 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’))
)

However, it doesn’t seem to capture the empty fields hence it is giving ‘error’ as the result.

So what is required is for a formula to look for those 6 fields, eliminate those empty fields and then look for the earliest date. Is this formula possible?

0 Replies 0