Help

Upcoming database upgrades. to improve our reliability at 03:30 UTC on Feb. 25 / 7:30pm PT on Feb. 24. Some users may briefly experience slow load times or error messages. Learn more here

Formula to choose Earliest Date (some fields are blank)

Topic Labels: Dates & Timezones
297 0
cancel
Showing results for 
Search instead for 
Did you mean: 

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