dateTime if then formula

Question

Hi,

I'm trying to calculate a time duration using an if then statement where:

if [Column A] is null then ([Column C] - [Column B])

else ([Column A] - [Column B])

endif

I've tried this formula:

if IsNull([Column A]) then [Column C] - [Column B]
ELSE [ColumnA] - [[Column A] - [Column B]]
endif

I'm receiving a "Formula: invalid type in subtraction operator" error.

Here is what I have for the Columns and Data Types:

FieldExampleTypeColumnA[Null]DateTimeColumnA1/15/2020 14:15DateTimeColumnB[Null]DateTimeColumnB1/15/2020 14:02DateTimeColumnC[Null]DateTimeColumnC1/15/2020 14:24DateTime

Thanks in advance

OllieClarke · Accepted Answer

Hi @BonusCup you'll need to use the datetimediff() function rather than just +/- So try: IF ISNULL([Column A]) THEN DATETIMEDIFF([Column C],[Column B],'') ELSE DATETIMEDIFF([Column A],[Column B],'') ENDIF is replaced by hours/days/months etc. There's more information on datetime functions here: link Hope that helps, Ollie

RolandSchubert · Accepted Answer

Hi @BonusCup ,

instead of using "-", use DateTimeDiff:

if !IsNull([Column A]) AND !IsNull([Column B]) then

DateTimeDiff([Column B],  [Column A], 'minutes')        or 'days'/'weeks' ...
ELSEIF !IsNull([Column B]) AND !IsNull([Column C]) THEN

DateTimeDiff([Column C],  [Column B], 'minutes')        or 'days'/'weeks' ...

ELSE

Null()
ENDIF

Best,

Roland