Get all the records whose date is before the date in the other column

[nikhilg19]

I have 2 columns A & B. want all the records where date in A < date in B.

Been using formula tool,

If [A]<=[B] then "yes" else "No" Endif 

but it differentiates the dates only based on the date number and not the dates based on month and year also.

[DavidThorpe]

Hi @nikhilg19 

You'll need to ensure your fields are of the date type, and use the datetime functions in the formula tool to test this, for example the below:

IF DateTimeDiff([Field1],[Field2],'days') <= 0 THEN 'yes' ELSE 'no' ENDIF

Tests if field1 (2019-01-20) has a positive/negative variance to field2 (2019-01-30) in number of days. In this case the value returned is a difference of -10 days, hence it returns a 'yes'.

I've attached a sample - hope this helps.

Thanks 

[nikhilg19]

Hi David,

I dont understand why it is showing as null for the output

[DavidThorpe · Accepted solution]

Your fields need to be of date type for the formula to work, they need converting to format 2019-01-15 etc.

You can do this within the formula, with the following expression:

IF DateTimeDiff(
DateTimeParse([Current Due Date],'%d/%m/%y'),
DateTimeParse([Last Friday],'%d/%m/%y')
,'days') <= 0
THEN 'yes'
ELSE 'no'
ENDIF

The DateTimeParse converts the dd/mm/yyyy string into a date type of yyyy-mm-dd, which Alteryx needs to form date calculations from

Thanks.

[DavidThorpe]

You can read up more on datetime functions here:

https://help.alteryx.com/2019.1/Reference/Functions.htm#Date/Time

Thanks!