topic Re: Dates Subtraction and conversion in Alteryx Designer Desktop Discussions

Dates Subtraction and conversion

vvissamsetty — Thu, 06 Jun 2019 17:52:42 GMT

I'm trying to subtract a column full of random dates - 12/31/2018. If the result < 366 then pick numbers from 'ST' and if > 366 then pick numbers from 'LT'. It would be great if someone can give me an idea on how to go with this.

Re: Dates Subtraction and conversion

Thableaus — Thu, 06 Jun 2019 17:59:51 GMT

Hi @vvissamsetty

In which format are your dates?

Cheers,

Re: Dates Subtraction and conversion

vvissamsetty — Thu, 06 Jun 2019 18:03:22 GMT

Hi @Thableaus , my dates are in the following format.

Re: Dates Subtraction and conversion

Thableaus — Thu, 06 Jun 2019 18:09:16 GMT

@vvissamsetty

IF your dates follow the same format as '12/31/2018' then I'd recommend this formula:

EDIT: Sorry, typo, change T for Y

- Use DateTimeParse function to change your date format to Alteryx format

- Use DateTimeDiff function to calculate difference from dates (in your case, subtract the dates)

Something like this:

IF DateTimeDiff(DateTimeParse([Date 1], "%m/%d/%Y"), '2018-12-31', "days") < 366 THEN [ST]
ELSE [LT] ENDIF

Cheers,

Re: Dates Subtraction and conversion

Thableaus — Thu, 06 Jun 2019 18:12:24 GMT

@vvissamsetty

It seems they are in this format: 31-Dec-18

So, this function would fit:

IF DateTimeDiff(DateTimeParse([Date 1], "%d-%b-%y"), '2018-12-31', "days") < 366 THEN [ST]
ELSE [LT] ENDIF

Cheers,

Re: Dates Subtraction and conversion

Thableaus — Thu, 06 Jun 2019 18:10:27 GMT

@vvissamsetty

Also, check this help document to understand how date parsing and specifiers work:

https://help.alteryx.com/10.1/Reference/DateTimeFunctions.htm

Cheers,

Re: Dates Subtraction and conversion

vvissamsetty — Thu, 06 Jun 2019 20:49:05 GMT

Thanks @Thableaus !