Using MAX to get latest date among 3 date columns

[Rachel_Erickson]

Hi folks,

I am fairly new to the formula game and I’m working on a formula where I have 3 columns with dates

{LOI Target}, {ETA to AMZ}, {Content Delivery Date}

and i’ll need to

1. calculate the latest date out of the three of these
2. Add 7 days to the latest date
3. Calculate to the next Tuesday
4. input date correctly into column

It’s a bit of a monster. Any help even in little pieces would be great.

I’ve been wandering the forums looking for help and this one was the closest formula to one of my needs (latest date out of the 3 dates).

So, is the following formula on the right track for what I want? I know that the MAX function doesn’t really bode well with dates but that’s about all I know.

IF(
OR(
{LOI Target},
{ETA TO AMZ},
{Content Delivery Date}
),
DATETIME_PARSE(
MAX(
IF({LOI Target}, VALUE(DATETIME_FORMAT({LOI Target},’MM/DD/YYYY’)), 999999999999999),
IF({ETA to AMZ}, VALUE(DATETIME_FORMAT({ETA to AMZ},’MM/DD/YYYY’)), 999999999999999)
) ,
IF({Content Delivery Date}, VALUE(DATETIME_FORMAT({Content Delivery Date},’MM/DD/YYYY’)), 999999999999999),
‘MM/DD/YYYY’
)
)

If I could nail down this first part (calculating the latest date out of the three), I can probably drag myself through the following formulas without much assistance.

Any and all help appreciated!!!

[kuovonne · Accepted solution]

The logic in your formula needs some adjusting. Because you are turning the dates into numbers, you need format the date as “YYYYMMDD”, the same as in the thread you referenced. Because you want the maximum date, instead of the minimum date, you need to have a small number (or no number) as the alternative instead of 999999999999999. You will also need to turn the number back into a string for parse it into a date.

IF(
  OR(
    {LOI Target},
    {ETA TO AMZ},
    {Content Delivery Date}
  ),
  DATETIME_PARSE(
    MAX(
      IF(
        {LOI Target}, 
        VALUE(DATETIME_FORMAT({LOI Target}, 'YYYYMMDD')), 
        0
      ),
      IF(
        {ETA to AMZ}, 
        VALUE(DATETIME_FORMAT({ETA to AMZ},'YYYYMMDD')),
        0
      ),
      IF(
        {Content Delivery Date}, 
        VALUE(DATETIME_FORMAT({Content Delivery Date},'YYYYMMDD')), 
        0
      )
    ) & "",
    'YYYYMMDD'
  )
) 

Honestly, if there will be only three dates, it might be simpler to do direct comparisons.

IF(
  IS_BEFORE({date1}, {date2}),
  IF(
    IS_BEFORE({date1}, {date3}),
    {date1},
    {date3}
  ),
  IF(
    IS_BEFORE({date2}, {date3}),
    {date2},
    {date3}
  ),
)

Because the formulas for calculating the latest date and calculating the next Tuesday both require multiple levels of testing, I recommend ending up with at least two formula fields: one formula field to calculate latest date and add 7 days, and a second formula to calculate the next Tuesday. Note that when developing your formula, you’ll probably use more formula fields.