Skip to main content
Solved

Find the maximum of three date fields on one table

  • June 22, 2021
  • 4 replies
  • 165 views
A
Forum|alt.badge.img+2
  • AM858
  • Participating Frequently

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.

Best answer by Kamille_Parks11

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

4 replies

Kamille_Parks11
Forum|alt.badge.img+27

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

A
Forum|alt.badge.img+2
  • AM858
  • Author
  • Participating Frequently
  • June 23, 2021

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.

R

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_Parks11
Forum|alt.badge.img+27

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'
   )
)
A
T
Justin_Barrett
Terms & ConditionsAccessibility statement