Help

The Community will be undergoing maintenance from Friday February 21 - Friday, February 28 and will be "read only" during this time. To learn more, check out our Announcements blog post.

Find the maximum of three date fields on one table

Topic Labels: Formulas
Solved
Jump to Solution
4699 4
cancel
Showing results for 
Search instead for 
Did you mean: 
AM858
6 - Interface Innovator
6 - Interface Innovator

I have three tables, Properties, Contacts, and Companies.

Each table has a Last Unsuccessful and Last Successful contact field. Sometimes I have contact information on one or multiple of these tables (i.e. if a company owns multiple properties then I’ll create the linked relationship).

I need a way to find the maximum date of when I last spoke to someone, whether I’m looking at either of the tables.

On the table below I have two lookup fields and a natural date field for both successful and unsuccessful contacts. I referred to the thread below but not sure why I am getting an error.

image

1 Solution

Accepted Solutions
Kamille_Parks
16 - Uranus
16 - Uranus

Alternate formula:

IF(
   OR({Date 1}, {Date 2}, {Date 3}), 
   DATETIME_PARSE(
      MAX(
         IF({Date 1}, VALUE(DATETIME_FORMAT({Date 1}, 'YYYYMMDD'))), 
         IF({Date 2}, VALUE(DATETIME_FORMAT({Date 2}, 'YYYYMMDD'))), 
         IF({Date 3}, VALUE(DATETIME_FORMAT({Date 3}, 'YYYYMMDD')))
      ), 
      'YYYYMMDD'
   )
)

See Solution in Thread

4 Replies 4

You’re probably getting an error because all your date fields are empty.

Ah i see. So how would I write the formula to recognize when there are date values in any of the three fields?

I didn’t show an image with contact information but there are cases where one, or two of the three date fields have a value, and I need to pull the max from the two date fields that have values.

@AM858 You can check if the date is empty first. If it is, you can assign a very early date like 1/1/1901. Now if all three are empty then it will return 1/1/1901 as max date. In this result, you can choose to leave the result empty. The following formula should work.

DATETIME_PARSE(MAX(IF(Date1=BLANK(),19010101,VALUE(DATETIME_FORMAT(Date1,'YYYYMMDD'))),IF(Date2=BLANK(),19010101,VALUE(DATETIME_FORMAT(Date2,'YYYYMMDD'))),IF(Date3=BLANK(),19010101,VALUE(DATETIME_FORMAT(Date3,'YYYYMMDD')))),'YYYYMMDD')

Let me know if you have questions.

Kamille_Parks
16 - Uranus
16 - Uranus

Alternate formula:

IF(
   OR({Date 1}, {Date 2}, {Date 3}), 
   DATETIME_PARSE(
      MAX(
         IF({Date 1}, VALUE(DATETIME_FORMAT({Date 1}, 'YYYYMMDD'))), 
         IF({Date 2}, VALUE(DATETIME_FORMAT({Date 2}, 'YYYYMMDD'))), 
         IF({Date 3}, VALUE(DATETIME_FORMAT({Date 3}, 'YYYYMMDD')))
      ), 
      'YYYYMMDD'
   )
)