Alteryx Designer Desktop Discussions

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

Identify if data is in Date format or string

mhtomines
7 - Meteor

Hi,

 

I'm trying to do a formula that says if the date is in string format parse it to become dateformat else if it already in date format leave it as is.

 

If [first_billing_date] = string then DateTimeParse([first_billing_date],"%m/%d/%Y") else [first_billing_date] endif

 

something like this

7 REPLIES 7
caltang
17 - Castor
17 - Castor

I believe the formula below helps you identify if a field is a string or not (T/F).

 

IsString()

 

 

So, you can try:

 

IF IsString([first_billing_date])
THEN DateTimeParse([first_billing_date],"%m/%d/%Y") 
ELSE [first_billing_date] 
ENDIF

 

 

Hope this helps! 

Best regards,
Calvin Tang
https://www.linkedin.com/in/calvintangkw/

Calvin Tang
Alteryx ACE
https://www.linkedin.com/in/calvintangkw/
ArnaldoSandoval
12 - Quasar

Hi @mhtomines 

 

You got two options.

  • The Browser Metadata tab, before implementing a formula or
  • The Field Infor tool

Both approaches allow you to check fields types.

 

hth

Arnaldo

Raj
14 - Magnetar

Formula tool will help you to achieve this 
use Is string formula.

mhtomines
7 - Meteor

Hi @caltang thank you for your response, however when I filter the date the data won't show up. The column first billing date is not constant "datetime" format sometimes it changes to string depending on the result of data download.

 

Attached is my workflow

 

 

ChrisWaspe
9 - Comet

Hi @mhtomines . I have expanded your example to include a string value which matches your format. When there is a combination of string and date value the column will be considered a string. You can then use Regular Expression to identify the values which match the string format you are looking for, and apply your formula. You have to create a new column as you can change data types within the formula tool.

Please feel free to reach for more info.

mhtomines
7 - Meteor

This works! thank you!!

caltang
17 - Castor
17 - Castor

Your original post did not include sample data nor a workflow, so I had assumed your formula required a tweak only. 

Glad @ChrisWaspe helped your case. @mhtomines @It will be useful to attach either sample data / your workflow in the future so that we can help you better/faster.

Calvin Tang
Alteryx ACE
https://www.linkedin.com/in/calvintangkw/
Labels