dateTime if then formula

[BonusCup]

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:

Field	Example	Type
ColumnA	[Null]	DateTime
ColumnA	1/15/2020 14:15	DateTime
ColumnB	[Null]	DateTime
ColumnB	1/15/2020 14:02	DateTime
ColumnC	[Null]	DateTime
ColumnC	1/15/2020 14:24	DateTime

Thanks in advance

[OllieClarke · Accepted solution]

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],'<unit>')

ELSE DATETIMEDIFF([Column A],[Column B],'<unit>')
ENDIF

<unit> is replaced by hours/days/months etc.
There's more information on datetime functions here: link 

Hope that helps,

Ollie

[RolandSchubert · Accepted solution]

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

[BonusCup]

@OllieClarke and @RolandSchubert 

Thank you both for the solutions.  Both worked.  Below are the outputs for both:

Since solution#2 was showing negative minutes I used 'abs' for both "DateTimeDiff".  Edit is:

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

abs(DateTimeDiff([Column B],  [Column A], 'minutes'))     
ELSEIF !IsNull([Column B]) AND !IsNull([Column C]) THEN

abs(DateTimeDiff([Column C],  [Column B], 'minutes'))    

ELSE

Null()
ENDIF

New result for Solution#2:

Thanks again to both!

[OllieClarke]

Hi @BonusCup the reason for the negative and positive numbers is because @RolandSchubert and I had our columns the other way round within the datetimediff() function. From the link I posted earlier:

DateTimeDiff (<date/time1>, <date/time2>, <units>) Subtract the second argument from the first and return it as a duration

---

so if <date/time1> is earlier than <date/time2> then the output of the datetimediff() function will be negative, otherwise it will be positive. 

I hope that clears things up a bit,

Ollie