# Re: Formula field that chooses earliest date OR gives pending if both blank

644 1
cancel
Showing results for
Did you mean:
5 - Automation Enthusiast

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?
2 Replies 2
11 - Venus

Hi @mrome1302.,

``````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.

5 - Automation Enthusiast

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?