Identify if data is in Date format or string

[mhtomines]

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

[caltang]

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/

[ArnaldoSandoval]

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]

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

[mhtomines]

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 · Accepted solution]

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]

This works! thank you!!

[caltang]

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.