How do I reference a date value column in an if statement formula (e.g. if(date

dominic_bardele

Hello, I am trying to write a formula (if statement) that references a date column. I've tried many various combinations of referencing the date value with and without the val() command and with various formatting of the actual date column, but I cannot get my if statement to work. My goal is to write something like if(date_column<2018-01-01,"TRUEoutput","FALSEoutput"). Nothing shows up in the sample output or the actual output so I must be writing my expression wrong. Any ideas? My date format is shows as "MM/dd/yyyyT00:00:00.000Z".

Alex_Combessie

Hello,

At the moment we do not support > or < operations in formulas for dates. Instead, you can use a formula like this:

if(diff(date_column.asDate("MM/dd/yyyy"), '2018-01-01'.asDate('yyyy-MM-dd'), 'days') < 0, 1, 0)

If your date column has the 'T' in its values, the format for the asDate() parameter will be "MM/dd/yyyy'T'HH:mm:ss.SSSZ

Cheers,

Alexandre

dominic_bardele

Thank you!!

MehdiH

Hi @dominic_bardele

It is now possible to use comparison operators for dates:

if(date_column.asDate("MM/dd/yyyy") < '2018-01-01'.asDate('yyyy-MM-dd'), 1, 0)

Or if date_column is already correctly parsed using the "Parse date" step:

if(date_column_parsed.asDate() < '2018-01-01'.asDate('yyyy-MM-dd'), 1, 0)

Cheers

Mehdi