Date Format Challenge

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

date.JPG

New Workflow_Amrg.yxmd

Input

Help

Preparation

Accepted answers

All comments

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

binu_acs

@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

@binu_acs here is a sample

sample.xlsx

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?

binu_acs

@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

Screenshot 2023-06-23 160037.png

Date_230623_1.yxmd

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

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703