Convert date format in bank statement

[cterrence]

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

[Yoshiro_Fujimori]

@cterrence , Could you also attach the input files (Bendigo.txt & Sites.xlsx)?

[Yoshiro_Fujimori]

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.

[cterrence]

Thank you very much for your reply. It doesnt work

[cterrence]

Hi - I have attached the text file in my first post (B.txt).  I have split the date from  - "TRAN EFF DATE 230228"

[Yoshiro_Fujimori · Accepted solution]

@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?

[Yoshiro_Fujimori]

I found a description in the help page as below. I guess this is the case you are facing...

https://help.alteryx.com/20223/designer/datetime-functions#specifiers

Limitation with 6-Digit Dates
Because up to 4 digits are read for the year, formats that are intended to have only 2 digits without a separator, such as a 6-digit date stamp (for example, %y%m%d for data resembling 170522 for May 22, 2017), are still read as 4 digits. To work around this limitation, you can...

Use 4 digits for the year (for example, 2017 instead of 17), depending on your range of dates.
Use the RegEx tool to insert a space after the first 2 digits in the string.

So it is not a bug but a known limitation.

[cterrence]

Thank you very much Yoshiro san.