Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

dateTime if then formula

BonusCup
11 - Bolide

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:

FieldExampleType
ColumnA[Null]DateTime
ColumnA1/15/2020 14:15DateTime
ColumnB[Null]DateTime
ColumnB1/15/2020 14:02DateTime
ColumnC[Null]DateTime
ColumnC1/15/2020 14:24DateTime

 

Thanks in advance

4 REPLIES 4
OllieClarke
15 - Aurora
15 - Aurora

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
16 - Nebula
16 - Nebula

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
11 - Bolide

@OllieClarke and @RolandSchubert 

 

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

 

BonusCup_0-1579187153449.png

 

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:

BonusCup_1-1579187473122.png

 

Thanks again to both!

OllieClarke
15 - Aurora
15 - Aurora

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

Labels
Top Solution Authors