Date Format Challenge

[cterrence]

Hi All

I have tried with my limited knowledge

I have to same the date format as the dashboards. The date format in the source is yyyy-mm-dd and it is string format. 

I want it in dd/mm/yyyy format. 

What I did

I used Datetime to change the format from string to date. I was successful. The metadata showed as "date"

I used a formula to change the "date" DateTimeFormat([DateTime_Out],"%d/%m/%Y") . The format did change to dd/mm/yyyy  but the format went back to V_String. 

I changed the format to date but got dd: 01/07/2022 is not a valid Date error

Regards

CT

[DataNath]

Hi @cterrence, Alteryx can only handle dates in ISO (YYYY-MM-DD) format (or YYYY-MM-DD HH:MM:SS for DateTime), if you want to maintain the date data type. Any other format needs to be stored as a string, hence why your dd/mm/yyyy format is throwing an error.

More info here - https://help.alteryx.com/20231/designer/datetime-functions

With you mentioning dashboards, the platform you're using the output in (like Tableau) will likely be able to automatically detect your dd/mm/yyyy string as a date or if not you're probably able to handle it there.

[ed_hayter]

Within Alteryx the Date Format will only hold "yyyy-mm-dd" so trying to turn a string to a date with "dd/mm/yyyy" will return the error you are seeing. Your question is a bit unclear as towhy it needs to be that format in Alteryx

[binuacs]

@cterrence Can you upload your input file?

[cterrence]

@DataNath  - I did leave the date format as string but when I checked the excel output. I was not able to sort the date as the format was in string. 

Also I wanted to maintain the same date naming convention in the dashboards

@ed_hayter - I did go through some topics, I was wondering if there is someway to change the format

@binuacs here is a sample 

[ed_hayter]

If your output is excel then what we want to change surely is not the datatype but the display settings of that date. I would recommend outputting as a date and checking how it appears in excel. If it is not to your liking perhaps alter the default display setting for dates in excel?

[binuacs]

@cterrence The Date field is already in YYYY-MM-DD format, you just need to apply the DateTimeFormat on this Date field would be enough

[DataNath]

@cterrence I'm afraid the only way to maintain a date data type in Alteryx is by keeping the YYYY-MM-DD, ISO format. If you output to Excel from this, you can sort on date. If you're using the output file in a visualisation software like Tableau/PowerBI, you'll be able to set a custom date format for the field there to get your desired dd/mm/yyyy appearance.

[cterrence]

Thank you for your suggestions....really appreciate it

Regards

CT