Skip to main content

Hi everyone,

I'm trying to create a formula field that pulls either one of two dates (depending on which one is the earliest) or gives "Pending Date" if both columns are empty. I've tried several different formulas and have gotten close, but I can't get the formula to fill in "pending date" still -- it always gives me #ERROR. Here are two formulas I have tried:

 
IF(
IS_BEFORE({Date 1}, {Date 2}), DATETIME_FORMAT({Date 1}, 'YYYY'),
IF({Date 1}=BLANK(), DATETIME_FORMAT({Date 2}, "YYYY", "Pending Date")))
and 
 
IF(
{Date 1}, DATETIME_FORMAT({Date 1}, 'YYYY'),
IF(NOT({Date 1}), DATETIME_FORMAT({Date 2}, 'YYYY'),
IF(NOT(AND({Date 1}, {Date 2}, "Pending Date")))))
 
Can someone tell me where I'm going wrong? 

Hi @mrome1302.,

How about this?

IF(OR({Data 1},{Data 2}),
DATETIME_FORMAT(
DATETIME_PARSE(
MIN(
IF({Data 1},VALUE(DATETIME_FORMAT({Data 1},"X"))),
IF({Data 2},VALUE(DATETIME_FORMAT({Data 2},"X")))
),
"X"
),
"YYYY"
),
"Pending Date"
)

It would be easier if functions such as MAX() and MIN() could be used for date types, but they need to be converted to numbers.

Hi @mrome1302.,

How about this?

IF(OR({Data 1},{Data 2}),
DATETIME_FORMAT(
DATETIME_PARSE(
MIN(
IF({Data 1},VALUE(DATETIME_FORMAT({Data 1},"X"))),
IF({Data 2},VALUE(DATETIME_FORMAT({Data 2},"X")))
),
"X"
),
"YYYY"
),
"Pending Date"
)

It would be easier if functions such as MAX() and MIN() could be used for date types, but they need to be converted to numbers.


Hi @Sho,

That worked, thank you so much!!!! Really appreciate the help (:

Do you know what the formula would be for if the date in the Date 1 column WASN'T before the date in the date 2 column? Like if I just wanted the formula to always pull Date 1 if it's filled in, pull Date 2 if Date 1 is empty, and then fill in "Pending Date" if both are empty?

 

Terms & Conditions