Convert Date

[NBPSE]

How do I convert this date from 2024-07-01 to 7/1/2024?

 

[alexnajm · Accepted solution]

DateTimeFormat is your friend here!

DateTimeFormat([Document Date], “%m/%d/%Y”)

[NBPSE]

I tried this formula and I am seeing this error. Does something need to be changed?

 

[alexnajm]

It’s hard to see the screenshot, but that error means something is off in the expression. In this case, you are trying to force a string into a date column by overwriting the Document Date column - you need to output it into a different column since you are no longer outputting a date but rather a string. 

[NBPSE]

This formula is creating the Document Date column. Creating a new column to output the newly formatted date is still giving me the same error. Does another step need to be added to format the date properly?

 

[alexnajm]

It is not creating the Document Date column - I can tell because you haven’t typed in Document Date as your column but rather selected it from the drop down. That would indicate it was created before this formula. 

This is also now a brand new formula, so not sure I see any error. 

[ChrisTX · Accepted solution]

I think this has been answered in your other post:

https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Convert-Number-Month-to-String...

For this post, it looks like your data type was Date and you needed it to be String so you could display the data in a specific format.

Using a Formula tool

• If you choose the option Create a new Field and type in "Document Date", Alteryx will use the same field that already exists. A new field will not be created.
• And if that existing field that is data type Date, you can't convert the data to a different data type (using a formula like DateTimeFormat) within the same field.  you need to create a NEW field
• note that the Multi-Field formula tool can change a data type using the same field, without creating a new field

Dates in Alteryx will always appear as YYYY-MM-DD

Data Types are always important information, when posting a question in the Community.

Chris

[NBPSE]

Yes you are correct, the data in the column was created by the formula above. I deleted the Formula and added a new Formula with a new column and that worked, thank you! Do you know how to output 7/1/2024 (remove the leading zeros of 07/01/2024 output)?

[alexnajm · Accepted solution]

Use a Replace function - replace([column], “0”, “”)

EDIT - that won’t work because of the 0 in 2024. I would either use regex or a trim left + replace first function. Something likw ReplaceFirst(TrimLeft([Column], “0”), “0”, “”)

[NBPSE]

That worked, this was my formula: ReplaceChar(TrimLeft(DateTimeFormat([Document Date], "%m/%e/%Y"),"0"), " ", "")