Hi
I have a bank statement in text format and I am unable to convert the transaction date into dd/mm/yyyy. I have used Dateformat tool or formula, but it just does not
Attached is the workflow
Please assist
Thanks
CT
@cterrence ,
Thanks for the data.
When I run the workflow to convert "230228" with "%y%m%d" format, I got warnings as below:
Formula (10) DATETIMEPARSE:
Cannot convert "230228" to a date/time with format "%y%m%d" and language "English":
Month number is out of range 1..12: '28'
The formula should be correct, but in some reason Alteryx Designer seems to confuse "28" as "month". (Maybe a bug, I don't know.)
Anyway, as a workaround, I added "20" at the head of the 6 digits to make it "20230228" and convert it with "%Y%m%d".
Then it worked.
NG Case = DateTimeParse([Date_yymmdd],"%y%m%d")
OK Case = DateTimeParse("20" + [Date_yymmdd],"%Y%m%d")
Transaction Date = DateTimeFormat([OK Case],"%d/%m/%Y")
Output
Does it work for you?
@cterrence , Could you also attach the input files (Bendigo.txt & Sites.xlsx)?
Not knowing the original date format, the date-time functions should work.
If your original format is YYYYMMDD in String type,
you may first convert it to Date with DateTimeParse function,
and then convert to your favorite format (in this case dd/mm/yyyy) with DateTimeFormat function.
DateTimeFormat(
DateTimeParse([Input],"%Y%m%d"),
"%d/%m/%Y")
If you have different date format in the input, you need to change the format param in DateTimeParse function.
For details of datetime functions, check the help page. https://help.alteryx.com/20223/designer/datetime-functions
If you still have any issue, please attach the input data of the date.
Thank you very much for your reply. It doesnt work
Hi - I have attached the text file in my first post (B.txt). I have split the date from - "TRAN EFF DATE 230228"
Thank you very much Yoshiro san.
