Help

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

Topic Labels: Dates & Timezones Formulas
661 2
cancel
Showing results for 
Search instead for 
Did you mean: 
mrome1302
5 - Automation Enthusiast
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
Sho
11 - Venus
11 - Venus

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.

mrome1302
5 - Automation Enthusiast
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?