Skip to main content

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} ,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?

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


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

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()))

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


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


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

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},...

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

Reply


Terms & Conditions