Help

Re: "if blank", "if not equal to blank"

1930 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Tyler_Dunlap
4 - Data Explorer
4 - Data Explorer

Having trouble locating a logical operator for “not equal to” and a way to reference a blank field

This formula is references 2 fields; Start Date and Finish Date. The formula is intended to return the age in days of the task I am tracking.

This should give me the amt of day between Start Date and Finish Date if Finish Date is not blank, else the amt of days between Start Date and Today

IF({Finish Date} [has a date/isnt blank] ,DATETIME_DIFF({Start Date}, {Finish Date}),DATETIME_DIFF({Start Date}, TODAY()))

What should I put here to make this formula work? What am I missing?

5 Replies 5
Peter_Todd1
5 - Automation Enthusiast
5 - Automation Enthusiast

I think that’s just a simple >"" so,

IF({Finish Date} > “”, DATETIME_DIFF({Finish Date}, {Start Date}, ‘days’),DATETIME_DIFF(TODAY(),{Start Date}, ‘days’))

You also could do (i didn’t test it though but used that NOT() function in another context):
IF(NOT({Finish Date} = ""), DATETIME_DIFF({Start Date}, {Finish Date}), DATETIME_DIFF({Start Date}, TODAY()))

This does not work for testing a number field as it evaluates NULL as zero.

If that’s so, would >0 work? There is an inequality operator != for string comparisons.

But to address the original post directly, you simply use the field as the test case, i.e. the first part of that formula would be:
IF({Finish Date},...

Yasutaka_Ito
5 - Automation Enthusiast
5 - Automation Enthusiast

@Tyler_Dunlap, the following formula will do what you want.
IF({Finish Date}, DATETIME_DIFF({Finish Date}, {Start Date}, "days"),DATETIME_DIFF(TODAY(), {Start Date}, "days"))

There are three things that were solved.

  1. Compare the date field isn’t blank.
  2. DATETIME_DIFF formula needs to be passed in the greater date value first.
  3. DATETIME_DIFF formula has a third parameter where you specify the intended unit value to return. So, if you want a value returned for “days”, you specify “days”.

Hope this make sense.